Slightly shorter version.

Sub colorit()
Dim Num As Long
Dim rng As Range
For Each rng In Selection
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
rng.Interior.ColorIndex = Num
Next rng
End Sub

If text values use Case is = "text": Num = 6

To get the colorindex numbers run this macro on a new worksheet.

Sub ListColors()
Dim a As Long
For a = 1 To 56
Cells(a, 1).Interior.ColorIndex = a
Cells(a, 2).Value = a
Next a
End Sub


Gord Dibben MS Excel MVP


On Tue, 21 Nov 2006 11:40:02 -0800, OC wrote:

Here's a simple vba code you can use.
Sub COLOR()
'
' COLOR Macro

For Each c In Range("your range")
If c.Value = "your info" Then
c.Interior.ColorIndex = 39
End If
Next c
For Each c In Range("your range")
If c.Value = "your info" Then
c.Interior.ColorIndex = 45
End If
Next c
For Each c In Range("your range")
If c.Value = "your info" Then
c.Interior.ColorIndex = 8
End If
Next c
For Each c In Range("your range")
If c.Value = "your info" Then
c.Interior.ColorIndex = 4
End If
Next c

End Sub

I'm sorry I can't find my table to tell you what all the colors are. If I
find it i'll send it to you.



"Paperback Writer" wrote:

I have an issue where I need to change the color of a cell based on a
particular value. Normally, I'd use conditional formatting, but this time I
have more than three conditions. In fact, I have five.

How should I do this?