Conditional formatting code from SydneyGeek

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,027
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]
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Try using the Worksheet_Calculate event instead of Worksheet_Change.
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,027
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.
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,027

ADVERTISEMENT

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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Code:
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,091
Messages
5,768,039
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top