ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Using VBA: Conditional Formatting of Row Color (https://www.excelbanter.com/excel-programming/403854-using-vba-conditional-formatting-row-color.html)

tepermanj
Using VBA: Conditional Formatting of Row Color
 
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
Using VBA: Conditional Formatting of Row Color
 
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

tepermanj
Using VBA: Conditional Formatting of Row Color
 
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

Bob Phillips
Using VBA: Conditional Formatting of Row Color
 
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

tepermanj
Using VBA: Conditional Formatting of Row Color
 
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

Bob Phillips
Using VBA: Conditional Formatting of Row Color
 
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

tepermanj
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

Dana DeLouis
Using VBA: Conditional Formatting of Row Color
 
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

--
Dana DeLouis

"tepermanj" wrote in message
...
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


All times are GMT +1. The time now is 02:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com