How can I set more than 3 conditions for cells?

[Cheng Joo]

I have a worksheet that I need to colour code the cells according to their
values and I need a total of 11 colours.

Conditional formatting only has up to 3 conditions and so I cannot use that
method.

Is there any other way that I can format my cells to automatically colour
code?

Thank you.

 

[Mike H]

Hi,

You could buy Excel 2007 or use the worksheet change event. In the sample
below I've done only 4 options but you can easilly extend this to 11. You
didn't say what values you wanted either but likewise you can easily alter
this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target.Value
Case Is = 1
icolor = 3
Case Is = 2
icolor = 4
Case Is = 3
icolor = 5
Case Is = 4
icolor = 6
Case Else
icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub


Mike

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") 'your values
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15) 'edit colorindex numbers
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub


Gord Dibben MS Excel MVP