Conditional formats get stuck

Question

I have a worksheet with two conditional format rules applied to a range of cells containing numbers.  The rules are based on a formula in another cell (A1) that returns a 1 or 0, depending on a user’s selection in a data validation list.  If the value of A1 is 1, percent formatting is applied to the range.  If the value is 0, whole number formatting is applied.  The conditional formatting applies correctly when I click “Apply” and “OK” to exit the CF dialogue window.  It also works correctly the first time a user causes the value in the cell A1 to change from 0 to 1 or vice versa.  However, the second time the value in the cell A1 changes, the conditional formatting is not applied.  It seems to get “stuck” on whatever the second format is set to.  Weirdly, if I click on a blank cell and select “Clear All” on the Home menu, the formats correct themselves.  Is this a bug?

Answer 1

OK. Let me know if you post the bug so I can have alook too.

Answer 2

renee_dss sent me the workbook and I can reproduce the problem in 2007.

It does not happen in 2010, so must be a bug in 2007 that has been fixed in 2010.

I haven't yet found a workaround.  Will look at it again tomorrow.

Answer 3

Calculation is set to automatic and the only possible values are 0 or 1.

Answer 4

I can't reproduce the problem.

If you'd like to put the file on a file sharing website (or email to me via the contact page of my website www.manville.org.uk ) I would be happy to take a look.

Answer 5

An empty cell also yields a zero as a result. So instead of just testing for zero, test for :

=AND(A1<>"",A1=0)

Is calculation set to automatic?