I have a macro enabled xlsm workbook (with macros) I have conditionaly
formatted cells that will not update automatically (any of them). The cells
will update correctly if I edit the rules, but only if I edit the rules. Auto
calculation is on. This workbook was created in Excel 2003 and the
conditional formatting worked fine, it was updated to 2007, everything else
works fine, macros included. None of the conditionally formatted cells will
update, original 2003 ones or new ones. If I create a new workbook, the same
conditional formatting (by formulas) works fine. Has anyone sen anything
similar or got any ideas at all??

I have a similar problem. It seems to relate to number formats only, and
further seems to relate to the order of the conditional rules.

I have a conditional format that attempts to change the number format of a
given cell based on the text value of another cell. There are three rules,
in this order: =<cell-ref="foo", =<cell-ref="foo1" and =<cell-ref="foo2".
A different number format is associated with each.

If I edit the rules (i.e. open and "ok" the Conditional Formatting Rules
Manager) the correct formatting will be set based on the value of <cell-ref.
If that value was "foo2" and I change it to "foo1", the number format
updates correctly. If I change it back to "foo2", the number format does not
update. If I then change the value to "foo", the format updates correctly.
But if I change back to "foo1" or "foo2", the format does not update. In
other words, if I change the value of <cell-ref to one that's "higher" in
the list of rules, format updating works. If I change it to one that's
"lower", it doesn't.

All of the foregoing assumes that the value of the formatted cell remains
constant. If I change the value of the formatted cell, formatting will
update correctly based on the value of <cell-ref. Then the behavior
described above pertains.

Interestingly, this only applies to number formatting. I set up an example
with font color, and it works correctly.

"ChrisACV" wrote:

I have a macro enabled xlsm workbook (with macros) I have conditionaly
formatted cells that will not update automatically (any of them). The cells
will update correctly if I edit the rules, but only if I edit the rules. Auto
calculation is on. This workbook was created in Excel 2003 and the
conditional formatting worked fine, it was updated to 2007, everything else
works fine, macros included. None of the conditionally formatted cells will
update, original 2003 ones or new ones. If I create a new workbook, the same
conditional formatting (by formulas) works fine. Has anyone sen anything
similar or got any ideas at all??

I just discovered that I mis-stated the situation in the paragraph starting
"All of the foregoing..." That paragraph should be replaced with:

"All of the foregoing assumes that the *content* of the formatted cell remains
constant. If I change the content of the formatted cell by placing a new
value or formula directly in the formatted cell, formatting will update
correctly based on the value of <cell-ref. Then the behavior described above
pertains. However, if I just change the *value* of the formatted cell
without changing the content (i.e., if the content is a formula, and I change
the value of another cell such that the value of, but not the formula in, the
formatted cell changes) no updating occurs and the behavior described above
pertains."

Any insights will be greatly appreciated.

"cisco kid" wrote:

I have a similar problem. It seems to relate to number formats only, and
further seems to relate to the order of the conditional rules.

I have a conditional format that attempts to change the number format of a
given cell based on the text value of another cell. There are three rules,
in this order: =<cell-ref="foo", =<cell-ref="foo1" and =<cell-ref="foo2".
A different number format is associated with each.

If I edit the rules (i.e. open and "ok" the Conditional Formatting Rules
Manager) the correct formatting will be set based on the value of <cell-ref.
If that value was "foo2" and I change it to "foo1", the number format
updates correctly. If I change it back to "foo2", the number format does not
update. If I then change the value to "foo", the format updates correctly.
But if I change back to "foo1" or "foo2", the format does not update. In
other words, if I change the value of <cell-ref to one that's "higher" in
the list of rules, format updating works. If I change it to one that's
"lower", it doesn't.

All of the foregoing assumes that the value of the formatted cell remains
constant. If I change the value of the formatted cell, formatting will
update correctly based on the value of <cell-ref. Then the behavior
described above pertains.

Interestingly, this only applies to number formatting. I set up an example
with font color, and it works correctly.

"ChrisACV" wrote:

I have a macro enabled xlsm workbook (with macros) I have conditionaly
formatted cells that will not update automatically (any of them). The cells
will update correctly if I edit the rules, but only if I edit the rules. Auto
calculation is on. This workbook was created in Excel 2003 and the
conditional formatting worked fine, it was updated to 2007, everything else
works fine, macros included. None of the conditionally formatted cells will
update, original 2003 ones or new ones. If I create a new workbook, the same
conditional formatting (by formulas) works fine. Has anyone sen anything
similar or got any ideas at all??