Conditional formatting rule precedence difficulties

[Luke]

I have three conditional formatting rules (in this order) that apply to a
20x20 array

1) If the cell values are 0, change the font color to white (essentially
"blanking" the cell)

2) If a certain cell value (say A2 - not in the array) equals 1 or 3, format
the array values as currency.

3) If that same cell (A2) equals 2, format the array values as integers.

The value of A2 changes based on which one of 3 radio buttons has been
selected. When I select the first or the third radio button, the value in A2
changes to 1 or 3 respectively and Excel 2007 dutifully formats the values as
currency. However, when I select the second radio button, the value in A2
changes to 2, but Excel does not format the array values as integers, leaving
them as currency instead. None of the "Stop If True" boxes are checked for
the three rules. If I switch the precedence of rule 2 and 3 (moving 3 up or
2 down), the opposite happens and the array values stay formatted as integers.

Can someone tell me how I can get the formatting to properly change based on
the value of A2?

 

[Victor Delta]

I have three conditional formatting rules (in this order) that apply to a
20x20 array

1) If the cell values are 0, change the font color to white (essentially
"blanking" the cell)

2) If a certain cell value (say A2 - not in the array) equals 1 or 3,
format
the array values as currency.

3) If that same cell (A2) equals 2, format the array values as integers.

The value of A2 changes based on which one of 3 radio buttons has been
selected. When I select the first or the third radio button, the value in
A2
changes to 1 or 3 respectively and Excel 2007 dutifully formats the values
as
currency. However, when I select the second radio button, the value in A2
changes to 2, but Excel does not format the array values as integers,
leaving
them as currency instead. None of the "Stop If True" boxes are checked
for
the three rules. If I switch the precedence of rule 2 and 3 (moving 3 up
or
2 down), the opposite happens and the array values stay formatted as
integers.

Can someone tell me how I can get the formatting to properly change based
on
the value of A2?

What are the actual formulas you are using for rules 2 & 3?

V

 

[Luke]

The actual formulas are:
=OR($A$2=1,$A$2=3) - to format cells as currency
=$A$2=2 - to format cells as integers

Changing the priority of the two doesn't help. If I put the last condition
in the second position, then all my values stay formatted as integers. For
some reason, it is getting stuck on that second condition, even without any
of the "Stop if true" boxes being checked.

 

[Victor Delta]

The actual formulas are:
=OR($A$2=1,$A$2=3) - to format cells as currency
=$A$2=2 - to format cells as integers

Changing the priority of the two doesn't help. If I put the last
condition
in the second position, then all my values stay formatted as integers.
For
some reason, it is getting stuck on that second condition, even without
any
of the "Stop if true" boxes being checked.

Strange! Things I would check:

Presumably the number in A2 is only an integer 0,1,2,3,4?
You could try putting the above formulas (with IFs) in blank cells and just
checking that they do behave as they should.

V