| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formatting for tab color | Excel Worksheet Functions | |||
| Conditional Formatting on Color | Excel Worksheet Functions | |||
| Color Conditional Formatting | Excel Worksheet Functions | |||
| Conditional color formatting entries have wild color. | Excel Discussion (Misc queries) | |||
| See conditional formatting Color value in vba | Excel Programming | |||
Linear Mode
