Andy,

Missed a line. Change both these lines:-

Case Else
cl.Interior.ColorIndex = xlNone
cl.Font.ColorIndex = 0

to

Case Else
selection.Interior.ColorIndex = xlNone
selection.Font.ColorIndex = 0

Mike

"andy" wrote:

hello Mike,

thanks, works just fine !
when i delete three values at a time, though, only the format of the first
cell selected returns to default. can default format can be applied to all
the selected fields?

thanks again.
andy

"Mike H" wrote:

Try this:-

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
cl.Interior.ColorIndex = xlNone
cl.Font.ColorIndex = 0
Exit Sub
End Select
Next cl
End If
End Sub

Mike

"andy" wrote:

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