Conditional Formatting

[Jeff Vandehey]

I need to do conditional formatting on more than 3 criteria. I have about 12
values I want to highlight w/ separate colors. Now, I can only select 3. If
I run those 3 (which successfully changes the highlight), the formatting is
deleted when I change the conditions and highlight colors.

Is there a way to add more than 3 conditions. If not, can I run them 3 at a
time, deselect the highlights that were just made, and run with the new
criteria?

Thanks for any help.

 

[JE McGimpsey]

I need to do conditional formatting on more than 3 criteria. I have about 12
values I want to highlight w/ separate colors. Now, I can only select 3. If
I run those 3 (which successfully changes the highlight), the formatting is
deleted when I change the conditions and highlight colors.

Is there a way to add more than 3 conditions. If not, can I run them 3 at a
time, deselect the highlights that were just made, and run with the new
criteria?

You can only apply three conditional formats at a time (for a total of
4, including the default format), unless you're conditionally formatting
fonts, in which case you can get a maximum of 6:

http://www.mcgimpsey.com/excel/conditional6.html

To do more than that, you'll need to use a VBA event macro. Say you want
to conditionally format input cells A1:A10, and you want twelve fill
colors plus the default. Ctrl-click on the worksheet tab and choose View
Code. In the module that opens, type or paste:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
With Range("A1:A10")
If Not Intersect(Target, .Cells) Is Nothing Then
For Each rCell In .Cells
Select Case rCell.Value
Case Is < 0
rCell.Interior.ColorIndex = 3
Case Is < 10
rCell.Interior.ColorIndex = 5
Case Is < 20
rCell.Interior.ColorIndex = 7
Case Is < 30
rCell.Interior.ColorIndex = 9
Case Is < 40
rCell.Interior.ColorIndex = 11
Case Is < 50
rCell.Interior.ColorIndex = 13
Case Is < 60
rCell.Interior.ColorIndex = 15
Case Is < 70
rCell.Interior.ColorIndex = 17
Case Is < 80
rCell.Interior.ColorIndex = 19
Case Is < 90
rCell.Interior.ColorIndex = 21
Case Is < 100
rCell.Interior.ColorIndex = 23
Case Is < 110
rCell.Interior.ColorIndex = 25
Case Else
rCell.Interior.ColorIndex = xlColorIndexNone
End Select
Next rCell
End If
End With
End Sub

change the criteria and fill colors to suit.

If your cells have formulae in them instead, use the
Worksheet_Calculate() event:


Private Sub Worksheet_Calculate()
For Each rCell In Range("A1:A10")
Select Case rCell.Value
Case Is < 0
'same as above
Case Else
rCell.Interior.ColorIndex = xlColorIndexNone
End Select
Next rCell
End Sub