Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Select Case Conditional Formatting Sub?
I've tried to use the Sub below (Posted by Julie D I think) as a work around
for more than three conditional formats and it works great IF I enter the data directly in the target cells. But, if the taget range is populated by a formula the color changes do not occur. Is there a way to modify this to work when the cells in the target range are formula driven? (Or, maybe I'm doing something wrong, any ideas?) Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("B6:B10")) Is Nothing Then With Target Select Case .Value Case 1: Target.Font.ColorIndex = 4 Case 2: Target.Font.ColorIndex = 3 Case 3: Target.Font.ColorIndex = 0 Case 4: Target.Font.ColorIndex = 6 Case 5: Target.Font.ColorIndex = 13 Case 6: Target.Font.ColorIndex = 46 Case 7: Target.Font.ColorIndex = 11 Case 8: Target.Font.ColorIndex = 7 Case 9: Target.Font.ColorIndex = 55 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
#2
|
|||
|
|||
Maybe...
Option Explicit Private Sub Worksheet_Calculate() Dim myCell As Range Dim myRng As Range Set myRng = Me.Range("b6:b10") On Error Resume Next 'continue with next cell For Each myCell In myRng.Cells With myCell Select Case .Value Case 1: myCell.Font.ColorIndex = 4 Case 2: myCell.Font.ColorIndex = 3 Case 3: myCell.Font.ColorIndex = 0 Case 4: myCell.Font.ColorIndex = 6 Case 5: myCell.Font.ColorIndex = 13 Case 6: myCell.Font.ColorIndex = 46 Case 7: myCell.Font.ColorIndex = 11 Case 8: myCell.Font.ColorIndex = 7 Case 9: myCell.Font.ColorIndex = 55 End Select End With Next myCell On Error GoTo 0 End Sub RCW wrote: I've tried to use the Sub below (Posted by Julie D I think) as a work around for more than three conditional formats and it works great IF I enter the data directly in the target cells. But, if the taget range is populated by a formula the color changes do not occur. Is there a way to modify this to work when the cells in the target range are formula driven? (Or, maybe I'm doing something wrong, any ideas?) Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("B6:B10")) Is Nothing Then With Target Select Case .Value Case 1: Target.Font.ColorIndex = 4 Case 2: Target.Font.ColorIndex = 3 Case 3: Target.Font.ColorIndex = 0 Case 4: Target.Font.ColorIndex = 6 Case 5: Target.Font.ColorIndex = 13 Case 6: Target.Font.ColorIndex = 46 Case 7: Target.Font.ColorIndex = 11 Case 8: Target.Font.ColorIndex = 7 Case 9: Target.Font.ColorIndex = 55 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- Dave Peterson |
#3
|
|||
|
|||
Dave, your solution works great...THANKS!
"RCW" wrote: I've tried to use the Sub below (Posted by Julie D I think) as a work around for more than three conditional formats and it works great IF I enter the data directly in the target cells. But, if the taget range is populated by a formula the color changes do not occur. Is there a way to modify this to work when the cells in the target range are formula driven? (Or, maybe I'm doing something wrong, any ideas?) Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("B6:B10")) Is Nothing Then With Target Select Case .Value Case 1: Target.Font.ColorIndex = 4 Case 2: Target.Font.ColorIndex = 3 Case 3: Target.Font.ColorIndex = 0 Case 4: Target.Font.ColorIndex = 6 Case 5: Target.Font.ColorIndex = 13 Case 6: Target.Font.ColorIndex = 46 Case 7: Target.Font.ColorIndex = 11 Case 8: Target.Font.ColorIndex = 7 Case 9: Target.Font.ColorIndex = 55 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Amount or Numbers in Words | New Users to Excel | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Spellnumber | Excel Worksheet Functions |