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 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! It’s similar to this piece of code below –
If <condition = true> then
<body of statements>
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!