Conditional formatting code from SydneyGeek

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,032
Office Version
  1. 365
Platform
  1. Windows
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try using the Worksheet_Calculate event instead of Worksheet_Change.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top