Conditional format : return to default format

[andy]

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

 

[Mike H]

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]

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]

Andy,

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


Mike

 

[Mike H]

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]

perfect ! thanks a lot.

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