hello,

the following code allows me to add more than 3 conditional formats to
fields in the range containing numbers -1, -0.5, 0.5 or 1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("C24:AF54,C601:AF91,C97:AF127"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case 1
cl.Interior.ColorIndex = 11
cl.Font.ColorIndex = 11
Case 0.5
cl.Interior.ColorIndex = 41
cl.Font.ColorIndex = 41
Case -1
cl.Interior.ColorIndex = 16
cl.Font.ColorIndex = 16
Case -0.5
cl.Interior.ColorIndex = 15
cl.Font.ColorIndex = 15
Case Else
Exit Sub
End Select
Next cl
End If
End Sub

i have two problems:
- when i delete the values, i would like excel to turn to default formatting
again automatically (i.e. no background and black color text).
- how can i apply the code to values that are already in the range (have
excel update the range automatically when i open the workbook) ?

as you notice, i'm new to VBA...

thanks.
andy