VBA Conditional Formatting

[ricci]

I'm working on a sheet that I need to have conditional formatting with more than 3 criteria in a certain range. I've worked out that I need to do this using VBA, I've put the code below and works fine.

I want to add in a 4th case, where if the contents of a cell in the range is deleted that the formatting is removed - I've tried Case ClearContents Case "" and Case " " but to no avail...any suggestion would be gratefully recieved! Thanks!



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target

Set WatchRange = Range("A1:A65536")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0
Target.Interior.ColorIndex = 35
Case 1
Target.Interior.ColorIndex = 35
Case 2
Target.Interior.ColorIndex = 44
Case 3
Target.Interior.ColorIndex = 3

End Select

End If

End Sub

 

[jim may]

Something like this,,,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Not IsNumeric(Target) Then Exit Sub

CellVal = Target

Set WatchRange = Range("A1:A65536")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0
Target.Interior.ColorIndex = 35
Case 1
Target.Interior.ColorIndex = 35
Case 2
Target.Interior.ColorIndex = 44
Case 3
Target.Interior.ColorIndex = 3
End Select
End If
End Sub

 

[ricci]

Thanks ever so much Jim - that works

 

[jim may]

Correction IN RED

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" And Target.Column = 1 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
......