Changing Cell Value doesn't update Conditional Format

Joined
May 24, 2007
Messages
26
I have a conditional format across many cells in a macro that if the cell value = 0 then the font will be white.

It works great originally, but if I put something else in the cell (other than 0) it doesn't automatically change the font to black, it stays as white. The only way that I can get it to update is to make a change to the cell, then go into Manage Rules, Edit the Rule, and then click Apply.

I'm sure there is something simple that I am missing, but I can't find any other posts that really answer my question so any help is appreciated.
Thanks in advance!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
Maybe it's because of the formula rule itself. Post the formula so someone can see if there's a loophole in it causing the problem.
 
Joined
May 24, 2007
Messages
26
Good idea! Basically I used the Conditional Formatting Tool. I selected cells B3:M25 and selected Format only cells that Contain.
Then in the rule description...
Cell Value - Equal to - 0

In the macro code it looks like this
Code:
    Range("B3:M25").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("B3").Select
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Not responsive to your question, but why don't you just use a format that surpresses display of zero, e.g., General;-General;;
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

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