Help with more than three conditions when formating.

[SMonczka]

Is it possible to have more than three conditions using conditional
formatting? I have a list of five products in a column. I want to
use a different color cell for each product. Product 1 would be
yellow, product 2 would be blue, product 3 would be green. I know how
to use conditional formatting to format each but I only seem to be
able to set up three conditions.

 

[David McRitchie]

When you can't use Conditional Formatting (more than 3 formats),
the next step is to use an Event Macro. See
http://www.mvps.org/dmcritchie/excel/event.htm#case
look at top for instructions to install, right click on sheet tab,
view code, then place your code in the sheet module.

 

[Frank Kabel]

Hi
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.

If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6 different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the entry in cell A1:A100 based on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub