Sheri

try something like this in the worksheet class module for the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Row < 3 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Select Case Int(Target.Value)
Case 1: Target.Interior.ColorIndex = 3 'red
Case 2: Target.Interior.ColorIndex = 36 'amber/yellow
Case 3: Target.Interior.ColorIndex = 27 'amber/dark yellow
Case 4: Target.Interior.ColorIndex = 4 'green
Case 5: Target.Interior.ColorIndex = 10 'dark green
Case Else: Target.Interior.ColorIndex = xlNone
End Select
End Sub

Regards

Trevor


"SP" wrote in message
...
I have a question from a user. They would like the cells to change colors
based on the value. The conditional formatting will not work, since they
have more than 3 conditions. Would it be better to create a VB script or
Formula. I am looking for direction to hopefully let them decided if
Excel
will continue to suit their needs.

TIA

Sheri