Conditional Formatting- 3 Condition Limit

[jobrumo]

I know this has been discussed at length in other threads but I did not find the problem I am having.

I have used the code that allows direct entry of a value into a cell which causes the formatting to change, but it does not work when that cell's value is generated by a FORMULA. In the code I have, if I make a direct entry exactly equal to the formula-generated value, the cell will change formatting.

Here's the code I use:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("B19:AF25")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 10
Case 11 To 15
icolor = 6
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 2
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

The formula is a nested "IF" statement that generates values between the ranges.

Thanks for any help.

 

[johnwm]

Welcome to Tek-Tips. Start by reading faq222-2244 to see how to get the best from these forums. It will guide you to post in the most appropriate forum.

As this forum is for VB5/6 only, and yours is a VBA question, you should repost this in forum707

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints