Solved: Formatting with Calulated Values

[GoKats78]

Column 2 is a calculated value (a sum of several other columns). I cannot get the code to work. When I enter the same value in the field it works, but not when it is a calulated value.

I know there must be a simple error in here somewhere...





Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
Select Case Target.Value
Case Is > 382: Target.Offset(0, 0).Interior.ColorIndex = 3: Target.Offset(0, 0).Font.ColorIndex = 2
Case Is > 315: Target.Offset(0, 0).Interior.ColorIndex = 30: Target.Offset(0, 0).Font.ColorIndex = 2
Case Is > 180: Target.Offset(0, 0).Interior.ColorIndex = 46: Target.Offset(0, 0).Font.ColorIndex = xlAutomatic
Case Is > 112: Target.Offset(0, 0).Interior.ColorIndex = 4: Target.Offset(0, 0).Font.ColorIndex = xlAutomatic
Case Is > 45: Target.Offset(0, 0).Interior.ColorIndex = 26: Target.Offset(0, 0).Font.ColorIndex = xlAutomatic
Case Else: Target.Offset(0, 0).Interior.ColorIndex = xlNone: Target.Offset(0, 0).Font.ColorIndex = xlAutomatic
End Select
End If

[GoKats78]

OK...I found out that I need to use Worksheet_Change...and here is what I came up with...but it is not working either!



Private Sub Worksheet_Calculate()
Dim rang As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rang In Range("B11:b2500")
If rang.Value >= 382 Then
rang.Interior.ColorIndex = 3
rang.Font.ColorIndex = 2
ElseIf rang >= 315 And rang < 382 Then
rang.Interior.ColorIndex = 30
rang.Font.ColorIndex = 2
ElseIf rang >= 180 And rang < 315 Then
rang.Interior.ColorIndex = 46
rang.Font.ColorIndex = xlAutomatic
ElseIf rang >= 112 And rang < 180 Then
rang.Interior.ColorIndex = 4
rang.Font.ColorIndex = xlAutomatic
ElseIf rang >= 45 And rang < 112 Then
rang.Interior.ColorIndex = 16
rang.Font.ColorIndex = 2
ElseIf rang >= 0 And rang < 45 Then
rang.Interior.ColorIndex = xlAutomatic
rang.Font.ColorIndex = xlAutomatic
End If
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Can and will someone help me out here?

[p45cal]

would this do it for you?Private Sub Worksheet_Calculate()
Dim rang As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rang In Range("B11:b2500")
Select Case rang.Value
Case Is >= 382
rang.Interior.ColorIndex = 3
rang.Font.ColorIndex = 2
Case Is >= 315
rang.Interior.ColorIndex = 30
rang.Font.ColorIndex = 2
Case Is >= 180
rang.Interior.ColorIndex = 46
rang.Font.ColorIndex = xlAutomatic
Case Is >= 112
rang.Interior.ColorIndex = 4
rang.Font.ColorIndex = xlAutomatic
Case Is >= 45
rang.Interior.ColorIndex = 16
rang.Font.ColorIndex = 2
Case Is >= 0
rang.Interior.ColorIndex = xlAutomatic
rang.Font.ColorIndex = xlAutomatic
Case Else
' Case Else is optional (do something if none of the above apply)
End Select
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[GoKats78]

That is exactly what I did!
Someone else suggested the Worksheet_calculate function...so I went out and found some code and adapting it to my needs...maybe it was your could I appropriated!
Thanks!