View Full Version : Excel 2003: conditional formatting

09-03-2007, 04:03 PM
I have set four conditional formats in a cell, the display format changing according to a result in another cell (Excel only allows up to three conditional formats, but you can get four by setting the "standard" cell format to be what's wanted for the normal/acceptable result in the other cell (and even four is a compromise on the six I really need!).

However, a quirk of conditional formatting is that the cell retains the last conditional format applied, even though none of the specified conditions are met and the the specified cell contents are not displayed. For example, assume the Result Cell has calculated a "score" that the Display Cell is conditionally formatted to react to with a bright yellow background and bold red font. If I then delete the inputs so that the Result Cell is blank, the Display Cell will stay bright yellow, ie it won't revert to standard format unless and until another "score" is calculated by the Result Cell, so causing the Display Cell to react anew.

Given that I have already used up the four different conditional formats allowed, is there any method short of VBA to persuade the Display Cell to automatically revert to a standard format?

10-03-2007, 11:42 AM
Hi, C/F works by applying the format selected if the condition is true. If all are false, then whatever format that was applied using standard formatting is displayed. If one of your 4 results needs to be 'no formatting' then effectively you only have 3 choices not 4.

Yes, for C/F conditions > 3 you have to use code although I don't know if Excel 2007 supplies more options. Excel has code that can run based upon actions, and these are called events. The two events you may require are the Change event (which runs when you physically type something into a cell) and the other one is the Calculate event (which runs when Excel recalculates the values in cells). If you are only typing values into a cell then you only need the Change event, but if you want to cover all situations then you need both.

A thing to note when using code is that it acts as if you had manually formatted the cells (just much quicker) so you would have to remove the C/F conditions as these over-ride what the code would have done if the CF conditions were True.

I can give you some example code if you advise the address/es of target cells, the conditions you want and what colours you want displayed under which condition.


14-03-2007, 04:48 PM
Thanks Graham, for your helpful explanations. After I posted the query, the conditional display requirements (which are to do with a financial analysis form's outputs) have been put back under review, and may be simplified. The question is whether we are simply setting the analysis to highlight when a specific and small range of conditions have not been met, ie to just "sound an alarm" that a closer analysis should be made, or to give a wider range of more finely calibrated ratings. Majority opinion, including mine, is now tending toward only displaying an alert when one of the significant KPIs is out of line.

15-03-2007, 11:48 AM
Cool, sounds as though you dont need anything. Just fyi I have done an example where Im looking at cells A1:A5 then colouring the background based upon its value. Changes to cells outside A1:A5 will have no effect.

In a new blank workbook do the following.
1) Right click a sheet tab and select View Code.
2) In the right hand windown paste the code below.
3) Select File then Close and Return to MS Excel to get back to the workbook
4) Enter some values in A1:A5

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, c As Range, RngIntersect As Range

'This range indicates which cells you want to apply formatting on if your condition is true
Set Rng = Range("A1:A5")

'Check to see whether the cell changed was in the range above
Set RngIntersect = Intersect(Rng, Target)
If Not RngIntersect Is Nothing Then
'The cell/s are in this range so now process. For/Next loops through all the cells
'that may have been changed. This will usually be 1 cell but may be multiple cells
'if you copied and pasted cells.
For Each c In Target
Select Case c.Value 'look at the value entered
Case Is >= 10: c.Interior.Color = vbCyan '>=10 then colour it cyan etc
Case Is >= 5: c.Interior.Color = vbGreen
Case Is >= 3: c.Interior.Color = vbRed
Case Is >= 1: c.Interior.Color = vbYellow
Case Else: c.Interior.ColorIndex = xlNone 'if none of the above is true have no formatting
End Select
Next c
End If

End Sub


16-03-2007, 04:16 AM
I made an elementary mistake with the code above.

Replace this line...
For Each c In Target

With this...
For Each c In RngIntersect

20-03-2007, 05:32 PM
Thanks Graham - will try the code (despite initially seeking a non-code solution!) complete with correction. It doesn't look like we will need the >3 conditional formats, but I just want to try your suggestion out of curiosity.