Conditional formatting not updating or refreshing as it did in earlier versions

Question

Conditional formatting not updating or refreshing as it did in earlier versions of excel

i have a recurring problem with old version and also new spreadsheets.

firstly, i have a number of spreadsheets from excel 2003 with conditional formatting. all very simple formulas [if (cell > 0,1,0)] was the original formula for example. this has translated over to excel 2010 but only partially works. For example, when i change the cell contents triggering the rule true/false it does not refresh the relevant cells' formatting. the only way i can get it to refresh is by scrolling away from the affected cell/cells and back again (so forcing the screen to refresh). unfortunately on some occasions even this does not work so i need to click on conditional formatting => manage rules and then click into the rule criteria so it goes into an edit mode and then click OK again to effectively reapply the rule. the rule is set to 'format only cells that contain' so only the cells in the defined range that trigger the rule (in this example are greater than zero) should have the conditional formatting applied. this issue is relevant whether i run the file in the old xls (xp/2003) format or update to xlsx 2010 format.

by contrast, in excel 2003 as soon as i changed the cell contents the conditional formatting immediately updated whether from on or off. whereas, as i explain above, it does not do this now in office 2010, which is quite odd.

even if i create a brand new blank excel sheet and create the same basic rule on one cell (or a range of cells) when i change it to true (set the cell to 1 for example, where rule = cell > 0) the formatting IS applied (it works!!) but when i delete the cell's contents the formatting remains (it does NOT work) until i scroll off the screen's view away from the affected cell and return (again forcing the screen to refresh).

i'm running windows 7 64-bit on a dell xps 1330 3gb laptop with an nvidia 8400 gs 128mb video card. using office 2010 32-bit professional

i have searched this forum and others and found a number of people have reported the same problem but so far i have been unable to find a solution. hopefully this all makes sense and someone can help?

thanks in advance.

Explanation / Answer

Hi,

I just tested this in 3 versions of Excel:- 2003, 2007 & 2010.

First thing is are you sure you described your problem correctly. You say you applied a CF formula of

>= 0

If you did then I'm not the least bit surprised that a blank cell remains formatted. let's say you applied this to A1. CF formulas evaluate as TRUE or FALSE and if TRUE the CF is applied. leave cell A1 blank and in another cell enter

=A1=0

You will see it evaluates as TRUE so the above CF would be applied.

However, this is where things become odd. If we use a CF formula of

>0

then a blank cell would evaluate as FALSE and the CF would not be applied. I did this in E2003 & E2007 and entered a positive number in the cell and the CF was applied. I deleted the number and the CF cleared but E2010 behaves differently

Cell a1 CF format formula is >0

Enter a positive number in A1 and the CF is applied. Enter a negative number and the CF is cleared.

BUT...Enter a positive number in a1 and then delete that number the CF doesn't clear and that is different to E2003 and e2007. With the cell blank the only way to clear the CF i can see is:-

1. Scroll the cell off the screen and then back on it.
2. With the cell selected tap F2 and enter.

In my opinion this is incorrect and a bug.

EDIT. Additional information.

In the above scenario the CF format was applied using

Conditional format|New rule|Format cells that contain...etc

Doing it that way the bug is evident.

Apply the same CF using

Conditional format|New rule|use formula to decide...etc

and use a CF formula of

=a1>0

Doing it this way the cell behaves as expected i.e delete a positive number and the CF formatting clears automatically.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.