Using VBA: Conditional Formatting of Row Color
Great!
Thanks!
"Bob Phillips" wrote:
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" wrote in message
...
Is there a way to make this case insensitive for the values in the Case
statements?
"Bob Phillips" wrote:
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" wrote in message
...
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" wrote:
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" wrote in message
...
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
|