It works for me.

You do realise it is using the worksheet_Calculate event, so it doesn't
change when you change those values.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"God Itself" wrote in message
...
Hi,

i have such a code:

Private Sub Worksheet_Calculate()
Dim rang As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rang In Range("P6:S6")

If rang.Value = "-" Then
rang.Interior.ColorIndex = 2
rang.Font.ColorIndex = 2
ElseIf rang = -1 And rang < -0.8 Then
rang.Interior.ColorIndex = 11
ElseIf rang = -0.8 And rang < -0.6 Then
rang.Interior.ColorIndex = 5
ElseIf rang = -0.6 And rang < -0.4 Then
rang.Interior.ColorIndex = 41
ElseIf rang = -0.4 And rang < -0.2 Then
rang.Interior.ColorIndex = 33
ElseIf rang = -0.2 And rang < 0 Then
rang.Interior.ColorIndex = 34
ElseIf rang = 0 And rang < 0.2 Then
rang.Interior.ColorIndex = 40
ElseIf rang = 0.2 And rang < 0.4 Then
rang.Interior.ColorIndex = 44
ElseIf rang = 0.4 And rang < 0.6 Then
rang.Interior.ColorIndex = 45
ElseIf rang = 0.6 And rang < 0.8 Then
rang.Interior.ColorIndex = 46
ElseIf rang = 0.8 And rang < 1 Then
rang.Interior.ColorIndex = 53
End If

If rang = -1 And rang < -0.6 Then
rang.Font.ColorIndex = 2
ElseIf rang = -0.6 And rang < 0.6 Then
rang.Font.ColorIndex = 1
ElseIf rang = 0.6 And rang < 1 Then
rang.Font.ColorIndex = 2
End If

Next

For Each rang In Range("P10:S10")

If rang.Value = "-" Then
rang.Interior.ColorIndex = 2
rang.Font.ColorIndex = 2
ElseIf rang = -1 And rang < -0.8 Then
rang.Interior.ColorIndex = 11
ElseIf rang = -0.8 And rang < -0.6 Then
rang.Interior.ColorIndex = 5
ElseIf rang = -0.6 And rang < -0.4 Then
rang.Interior.ColorIndex = 41
ElseIf rang = -0.4 And rang < -0.2 Then
rang.Interior.ColorIndex = 33
ElseIf rang = -0.2 And rang < 0 Then
rang.Interior.ColorIndex = 34
ElseIf rang = 0 And rang < 0.2 Then
rang.Interior.ColorIndex = 40
ElseIf rang = 0.2 And rang < 0.4 Then
rang.Interior.ColorIndex = 44
ElseIf rang = 0.4 And rang < 0.6 Then
rang.Interior.ColorIndex = 45
ElseIf rang = 0.6 And rang < 0.8 Then
rang.Interior.ColorIndex = 46
ElseIf rang = 0.8 And rang < 1 Then
rang.Interior.ColorIndex = 53
End If

If rang = -1 And rang < -0.6 Then
rang.Font.ColorIndex = 2
ElseIf rang = -0.6 And rang < 0.6 Then
rang.Font.ColorIndex = 1
ElseIf rang = 0.6 And rang < 1 Then
rang.Font.ColorIndex = 2
End If

Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

unfortunatelly, cells in ranges P6:S6 and P10:S10 are not being coloured
and i have no idea why

how can i check this