Using VBA: Conditional Formatting of Row Color

[tepermanj]

I found the below script in one of the threads. It works fine except for one
thing. When you delete the contents of a cell, then the color does not
revert to No Fill.
Can anyone help? Thanks.
I tried adding this but it didn't work.

Case ""
Target.EntireRow.Interior.ColorIndex = 0


Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.EntireRow.Interior.ColorIndex = 5
Case "Cat"
Target.EntireRow.Interior.ColorIndex = 10
Case "Other"
Target.EntireRow.Interior.ColorIndex = 6
Case "Rabbit"
Target.EntireRow.Interior.ColorIndex = 46
Case "Goat"
Target.EntireRow.Interior.ColorIndex = 45
End Select
End If
End Sub

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Dog"
Target.EntireRow.Interior.ColorIndex = 5
Case "Cat"
Target.EntireRow.Interior.ColorIndex = 10
Case "Other"
Target.EntireRow.Interior.ColorIndex = 6
Case "Rabbit"
Target.EntireRow.Interior.ColorIndex = 46
Case "Goat"
Target.EntireRow.Interior.ColorIndex = 45
Case Empty, ""
Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[tepermanj]

Beauty, thanks!
Why don't I have to make the following declaration?
Const xlColorIndexNone = -4142
(Also, I added a Case Else statement to take care of all other values).

 

[Bob Phillips]

because it is a built-in constant.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[tepermanj]

Is there a way to make this case insensitive for the values in the Case
statements?

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target.Value
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case LCase(CellVal)
Case "dog"
Target.EntireRow.Interior.ColorIndex = 5
Case "cat"
Target.EntireRow.Interior.ColorIndex = 10
Case "ther"
Target.EntireRow.Interior.ColorIndex = 6
Case "rabbit"
Target.EntireRow.Interior.ColorIndex = 46
Case "goat"
Target.EntireRow.Interior.ColorIndex = 45
Case Empty, ""
Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[tepermanj]

Great!
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target.Value
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case LCase(CellVal)
Case "dog"
Target.EntireRow.Interior.ColorIndex = 5
Case "cat"
Target.EntireRow.Interior.ColorIndex = 10
Case "ther"
Target.EntireRow.Interior.ColorIndex = 6
Case "rabbit"
Target.EntireRow.Interior.ColorIndex = 46
Case "goat"
Target.EntireRow.Interior.ColorIndex = 45
Case Empty, ""
Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dana DeLouis]

You have a solution, but here's just another variation.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
Dim CI As Long

If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target.Value
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case LCase(CellVal)
Case "dog": CI = 5
Case "cat": CI = 10
Case "ther": CI = 6
Case "rabbit": CI = 46
Case "goat": CI = 45
Case Empty, CI = xlColorIndexNone
End Select
Target.EntireRow.Interior.ColorIndex = CI
End If
End Sub