Copy/paste this code to a sheet module.

Edit to suit.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If 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

On Mon, 13 Jul 2009 13:19:01 -0700, Charlie Lam
wrote:

I have cells that I want to change color automatically when I input a certain
value, Letter or Number. However the conditional format only allows me to do
3 conditions/colors. Is there a way I can add more then 3 conditions?