Conditional Formatting VBA code - need help!

[LMS0214]

I have the code below in my worksheet and I am not receiving any error messages however nothing is happening in the cell whenever a change is made? Someone please help.

The conditional formatting only applies to 1 cell (S25), I need the cell to change based on the number in the cell. For example, if 6,003 is in the cell then I need it to be green, if 25,004 is in the cell then I need it to be tan, etc...)

Am I missing a piece of code or can anyone provide insight on the possible problem?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("S25")) Is Nothing Then
Select Case Target

Case 1 To 6000
icolor = 35
Case 6001 To 25000
icolor = 37
Case 25001 To 50000

icolor = 40
Case 50001 To 75000
icolor = 4
Case 75001 To 100000
icolor = 6
Case 100001 To 400000
icolor = 3

End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

 

[Jerry Sullivan]

Welcome to the Board!

The code you posted worked for me.

Two things to check:
Are macros enabled?
if so...
Make sure Application.EnableEvents = True

 

[LMS0214]

Thank you!! I enabled macros and added that code - both of those fixed the problem assuming I type in the value in that cell. My next question is if there is anyway to get the code to work based on the value of another hidden cell. For example, I want S25 (which is the cell with the formatting) to have the formula "=a25" and populate/change colors based on the value of A25.

As of now, the code works whenever I type in 25,000 in S25 but if I use the formula =a25 where A25 has 25000 in it the formatting does not change. Any ideas?

 

[Jerry Sullivan]

The Target parameter of the Worksheet_Change event will be only the Range that is changed directly by the user entry. In the case you gave, the fact that the value of S25 has changed through a change to A1 will not cause S25 to be included in the Target.

To do what you describe, you can change the Range that is being "watched" to A1 and update the formatting in S25 if A1 changes....

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Select Case Target
           'Your code....
        End Select
        Range("S25").Interior.ColorIndex = icolor
    End If
End Sub

Or you could more broadly update the formatting of S25 anytime there is a change to the entire worksheet. This option is causes the update to happen more often than necessary; however for relatively short code like this, there shouldn't be a noticable loss of speed.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    Select Case Range("A1")
       'Your code....
    End Select
    Range("S25").Interior.ColorIndex = icolor
End Sub

 

[LMS0214]

Thank you for your help! That fixed it!