Excel 2010 does not appear to re-apply conditional formatting when cell values change as a result of a formula.
For example, enter the following in a new spreadsheet:
* A1 = 1
* A2 = 10
* A3 = A1 * A2
Now, let's apply the following conditional formatting rules:
1. If A3 >= 1, then highlight in colour 1
2. If A3 <=-1, then highlight in colour 2
Scenarios:
i. If A1 = 1, then colour 1 is applied is applied to A3 -->ok.
ii. If A2 is changed to -1, then A3 becomes -10, but colour 1 is still applied to A3 -->incorrect. Selecting "Apply" in the Conditional Formatting Manager will apply the correct colour, though.
iii. If you enter -20 in A3 manually (i.e. you override the formula), then colour 2 is applied -->ok.
So, the issue appears to be with re-evaluating the formatting applied to formula-results.
I have my calcs switched to manual, but I re-calculate the workbook (F9 or Shift-F9).
Is this a known bug, or do I need to change any of my settings to overcome this rather issue?