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

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

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
>