A lot of the time, there are things that totally baffle you when you least expected them – limitations, extensions, workarounds, unavailability etc. And that’s exactly what I’d like to uncover in my ‘Did you know’ series! Without much talk in this one, I really want to keep these ones short and succinct, here’s the first…

DID YOU KNOW!

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).

All this brings up the argument of why even give such a facility when it’s not really much use! This may not seem like a big limitation, but only when you really try to put this feature to good use will you be irked!

The Situation – I wanted a cell to be formatted in 4 different ways based on filter criteria.

  1. A dollar format if it’s tabulating currency
  2. A number format for frequency counting table values
  3. A percentage when applicable
  4. and finally when ‘#N/A’ appears, I’d like it to dissolve into the background!

And appears this is not what the conditional formatting can handle!

The workaround – VBA! Always!

Additional tip

There’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! If I had to explain using code, it’s more like

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! Although, given this limitation of just 3 formats, I’m not sure how much help this is – but surely, if you have just 3, this is a good one.

About these ads