If it's Tuesday, it must be mauve

Conditional formatting is one of Excel's better features. It allows you to preset certain font styles, colors, and cell-background colors based on cell values.

This can be very useful for highlighting important information and values outside an accepted range or providing a visual cue to associate value ranges with color codes.

The best part is that conditional formatting is very easy to set up.

Just click the cells you'd like to format and select Format >Conditional Formatting. The Conditional Formatting dialog box lets you set up the conditions by which the formatting of the cell will occur.

You pick the operator (between, equal to, less than, etc.) and the value or range of values. Click Format to open the Format Cells dialog box, where you can select the colors and styles to be used.

Each cell can have several conditional formats. For example, you might say that if a certain cell's value is between 20 and 50, the text should be blue on a yellow background.

However, you can format that same cell to exhibit red, bolded text on a green background if it contains a value between 51 and 100.




Before 2007, you could use up to three conditions, but earlier versions of Excel can be tricked to use more if it should become necessary.

Oz Grid:
Excel VBA Macro Code to Get Around Excel's 3 Criteria Limit in Conditional Formatting

GR Business Process Solutions:
Graham Barrow and Ray Blake
Highlight the current or past month in Excel with conditional formatting

Chip Pearson:
Conditional Formatting

Also

Compatibility in Excel 2007

Conditional Format Functions in Excel 2007

Adding Customized Rules to Excel 2007

Demo:
Data takes shape with conditional formatting



See all Topics
excel

Labels: Customize, Formats, Tips, Tutorials

Choose your own location

Word

you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.

Excel

Tools>Options. On the "General" tab change the default location.

PowerPoint

uses Tools>Options and the "Save" tab.

Access

Tools>Options and the "General" tab for Databases and Projects

Publisher

Tools>Options "General".

Outlook

will make you take an underground tour into the Registry to change the location to save e-mail attachments.

FrontPage/Expression Web

appears to require the same sort of spelunking.

Labels: Customize, General

Roll your own OA

The Office Assistant has taken a lot of hits, but it is missing in 2007, but still around in earlier versions.

If you want to play with it, see John Walkenbach's Create A Fake Clippy



Here is a creature you could use to replace Clippy:
Nerd Bird

There are other articles in the MS library about Agents and Assistants:
Animating Office Applications with Microsoft Agent

And:
Using the Office Assistant to Display Help

Programming the Office Assistant

The VBA help file give a list of motions for the assistants. Look for Assistants>Animation.
The code is simple:

With Assistant
.On = True
.Visible = True
.Move xLeft:= 400, yTop:= 300
.MoveWhenInTheWay = True
.TipOfDay = True
.Animation = msoAnimationGreeting
End With

Here are a few of the actions:

• msoAnimationBeginSpeaking
  
• msoAnimationCheckingSomething
  
• msoAnimationGetArtsy
  
• msoAnimationGetTechy
  
• msoAnimationGetWizardy
  
• msoAnimationRestPose
  
• msoAnimationSendingMail
  
• msoAnimationThinking
  
• msoAnimationWorkingAtSomething
  
• msoAnimationWritingNotingSomething
  
• msoAnimationCheckingSomething
  
• msoAnimationGetTechy
  
• msoAnimationListensToComputer

Labels: Customize, Macros

Level talk

1. Close any programs that are running.
   
   
2. Click Start, click Run, type regedit in the Open box, and then click OK.
   
   
3. In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:
   
   Microsoft Office Excel 2007
   

   HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

   
   Microsoft Office Excel 2003 uses Office\11.0\
   Microsoft Excel 2002 uses Office\10.0\
   Microsoft Excel 2000 uses Office\9.0\
   
   
4. On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.
   
   
5. On the Edit menu, click Modify.
   
   
6. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.
   
   
7. Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.

Sub ClearUndo()
    Range("A1").Copy Range("A1")
End Sub

Labels: Customize, General, Macros, Tips

Point to the spot

Labels: Customize, Formats, Tips

A nice sparkle

Labels: Customize, Gadgets, Macros

Let the add-ins begin

Learn how to customize the Ribbon user interface (UI) in the 2007 Microsoft Office release. Also learn how new features in Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System support RAD development of Ribbon customizations. (40 printed pages)

Labels: Customize