Brian,

Try the code below. Note that the code should go into the sheet's
codemodule, not a standard codemodule. You might want to change

Select Case cRange.Value
to
Select Case Application.Round(cRange.Value,2)

where the 2 is your displayed decimals, so that your colors match with your
displayed values.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRange As Range

Set cRange = Intersect(Range("A1:M99"), Target(1))
If cRange Is Nothing Then Exit Sub

With cRange.Interior
.ColorIndex = xlNone
Select Case cRange.Value
Case Is < 0
.ColorIndex = xlNone
Case Is < 1.1
.ColorIndex = 3
Case Is < 2.5
.ColorIndex = 6
Case Is < 3.5
.ColorIndex = 4
Case Is <= 4#
.ColorIndex = 5
End Select
End With
End Sub


"Brian in FT W." wrote in message
...
I'm trying to write an event macro for a worksheet that will change a
cell's
color based on the outcome of various formulas.

I can get the Conditional Format to work for 3 items, however I have 4
variables.

If a number is between 4-3.5, Blue
If a number is between 3.49-2.5, Green
If a number is between 2.49-1.10, Yellow
If a number is between 1.09-0, Red

Below is what I tried to use, and I assume that is is completely wrong.

Can anyone offer a solution or advice? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim vNumber As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range

Set cRange = Intersect(Range("A1:M99"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub


vNumber = vNumber(Left(cell.Value & " ", 1))

vColor = 0
Select Case vNumber
Number "4.0:3.5"
vColor = 5
Number "3.499999:2.5"
vColor = 4
Number "2.49999:1.10"
vColor = 6
Number "1.09999:0"
vColor = 3

End Select
Application.EnableEvents = False
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True
End Sub