Conditional formatting?

[John]

When doing conditional formatting is there a way to get more than 3
conditions? I wanted to do a simple thing like put in value 1 and background
goes green, 2 = red, 3 = purple, 4 = orange and 5 = blue.

 

[Frank Kabel]

Hi John
conditional format only accepts 3 conditions though you have a fourth
if you include the default format. So maybe this is sufficient for you.

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)

HTH
Frank

 

[Bob Phillips]

John,

We had the same question yesterday, and this was my answer then.

Essentially you need VBA programming. Here is an example

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Count = 1 Then
If .Column = 1 Then
Select Case .Value
Case Is = 1
.Interior.ColorIndex = 3 'red
Case Is = 2
.Interior.ColorIndex = 38 'pink
Case Is = 3
.Interior.ColorIndex = 4 'green
Case Is = 4
.Interior.ColorIndex = 6 'yellow
Case Is = 5
.Interior.ColorIndex = 8 'majenta
Case Is = 6
.Interior.ColorIndex = 5 'blue
Case Is = 7
.Interior.ColorIndex = 15 'grey
Case Is = 8
.Interior.ColorIndex = 38 'rose
Case Is = 9
.Interior.ColorIndex = 1 'teal
Case Else 'none of the above numbers
Exit Sub
End Select
End If
End If
End With

ws_exit:
Application.EnableEvents = True

End Sub

This is worksheet code and goes in the worksheet coide module (right-click
on the sheet name tab, select the View Code menu option, and paste the code
in).


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)