Conditional format : return to default format

A

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
 
M

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
 
A

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
 
M

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
 
M

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
 
A

andy

perfect ! thanks a lot.


Mike H said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top