Thsi si a very popular question. It is asked a few times a week. Here is
the answer somebody provided earlier this week. It shows four conditions but
can easily be changes to havve many more conditions Just add more case
statements to the function.

-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub



"Hawksby" wrote:

Is there a way of having 5 or more conditional formats?