Conditional formatting code from SydneyGeek

[jcaptchaos2]

SyndeyGeek posted this code which is exactly what I need but I can't get it to work on formulas, is there a way? If I type the number in the cell it works fine but if I have a formula in the cell that the result changes the color does not.

Dennis
Thanks for this code I found by searching, Can you tell me if there is anyway to get it to work if the cell is a formula?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer
' Multiple Conditional Format
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("E10:E208")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
Select Case Target.Value
Case Is > Range("J2").Value
iColor = 20
Case Is > Range("J3").Value
iColor = 44
Case Is > Range("J4").Value
iColor = 15
Case Is > Range("J5").Value
iColor = 46
Case Else
iColor = 0
End Select
Target.Interior.ColorIndex = iColor
End Sub
The break values are in J2:J5, sorted descending. They could just as easily be links to another sheet.
BTW, I used Select Case because it's easier to maintain if you add or remove conditions.
You'll also need this code in a standard module to set the starting formats:
Code:
Sub SetFormats()
Dim iColor As Integer
Dim c as Range

For Each c in Range("E10:E208")
Select Case c.Value
Case Is > Range("J2").Value
iColor = 20
Case Is > Range("J3").Value
iColor = 44
Case Is > Range("J4").Value
iColor = 15
Case Is > Range("J5").Value
iColor = 46
Case Else
iColor = 0
End Select
c.Interior.ColorIndex = iColor
Next c
End Sub
[/Qoute]

 

[Lewiy]

Try using the Worksheet_Calculate event instead of Worksheet_Change.

 

[jcaptchaos2]

I'm not a big code person so I do a lot of recording macros and asigning them to buttons, when I make the change that you suggested my other macros error out and go to the debugger.

 

[brian.wethington]

See the following thread for changes from a regular module VBA to a Worksheet change event to ultimately a worksheet calculate event. Hopefully it step you through a bit.

http://www.mrexcel.com/board2/viewtopic.php?t=282960&highlight=

 

[jcaptchaos2]

Ok this worked well, thanks. One more question what would I add to this so if the value is 100 the font is white? 200 the font is green or what ever color?

Option Compare Text
Option Explicit
Private Sub Worksheet_Calculate()

Dim mycolor As Long, Ce As Variant
For Each Ce In Range("O87:O132")
Select Case Ce.Value
Case "100"
mycolor = 3
Case "200"
mycolor = 44
Case "300"
mycolor = 2
Case "400"
mycolor = 4
Case "None"
mycolor = 2
Case "NA"
mycolor = 15
Case Else
mycolor = 0
End Select
Range(Cells(Ce.Row, "B"), Cells(Ce.Row, "M")).Interior.ColorIndex = mycolor
Next Ce

End Sub

 

[brian.wethington]

Option Compare Text 
Option Explicit 
Private Sub Worksheet_Calculate() 

Dim mycolor As Long, Ce As Variant, fcolor As Long 
For Each Ce In Range("O87:O132") 
Select Case Ce.Value 
Case "100" 
mycolor = 3 
fcolor = 8
Case "200" 
mycolor = 44 
fcolor 0
Case "300" 
mycolor = 2 
fcolor 12
Case "400" 
mycolor = 4 
fcolor = 6
Case "None" 
mycolor = 0 
Case "NA" 
mycolor = 15 
Case Else 
mycolor = 0 
End Select 
With Range(Cells(Ce.Row, "B"), Cells(Ce.Row, "M"))
    .Interior.ColorIndex = mycolor 
    .Font.ColorIndex = fcolor
End With
Next Ce 

End Sub

Note that I just threw fcolor in so you will need to set it to the appropriate color just change the fcolor under the category.

Hope this helps!

 

[jcaptchaos2]

Yes, Thanks a lot Brian.