Did you know! Conditional Formatting in Excel 97-2003/2007 limits to 3

The Conditional Formatting of Excel 97-2003/2007 has the major limitation that only 3 formats can be specified (excluding the one that’s already present in the cell).

Lets take an example – Can I format a cell in 4 different ways as below?
1. A dollar format if it’s tabulating currency
2. A number format for frequency counting table values
3. A percentage when applicable
4. When ‘#N/A’ appears, I’d like it to dissolve into the background (Font = white)

And Excel throws up an error.

The workaroundVBA! Always!

Additional tipThere’s a ‘Stop if True’ feature in the Conditional Formatting rules. Conditional formatting executes the format rules in the order in which they are added! So, if you select this feature, the Conditional Formatting stops at that particular rule, and doesn’t go further! It’s similar to this piece of code below –

If <condition = true> then
    <body of statements>
    <exit sub>
End if

If we ended up using an ‘If’ with an ‘Exit Sub’, that’s exactly what Stop if True’ would do – exit the function if any of the ‘If’s’ is true!

About these ads

One thought on “Did you know! Conditional Formatting in Excel 97-2003/2007 limits to 3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s