Conditional formatting not updating automatically (Excel 2010)

Short title:

Automatic formatting is not always redrawn

Long title

Automatic formatting is not always redrawn for a cell, when that conditional formatting is based on one or more different cell's value(s).

Setup worksheet for Repro:

  1. Format Columns $A:$B as dates, English (U.S.), Type "01-Mar-09"
  2. $A$1 == "Due Date"
    • $A$2:$A$7 == 05-Dec-2009
  3. $B$1 == "Date Filed"
  4. $C$1 == "Notes"
    • $C$3 == "Some Note"
    • $C$5 == "Other note"
    • $C$7 == "Third Note"
  5. $D$1 == "Value to Enter in Date Filed"
    • $B$2:$B$3 == 05-Dec-2009
    • $B$4:$B$5 == 06-Dec-2009
  6. $E$1 == Formatting Expected
    • $E$1:$E$5 == =AND(NOT(ADDRESS(ROW(),6)), NOT(ADDRESS(ROW(),7)), ADDRESS(ROW(),8))
  7. $F$1 == Rule #1 result
    • $F$1:$F$5 == =ISBLANK(INDIRECT(ADDRESS(ROW(),2)))
  8. $G$1 == Rule #2 result
    • $E$1:$E$5 == =INDIRECT(ADDRESS(ROW(),2))-INDIRECT(ADDRESS(ROW(), 1))<=0
  9. $H$1 == Rule #3 result
    • $E$1:$E$5 == =ISBLANK(INDIRECT(ADDRESS(ROW(),3)))
  10. Column C == Notes

Setup conditional formatting on Column C as follows:

Rule #1 avoids formatting if DateFiled is blank, no formatting, stops evaluation if true:

  • =ISBLANK(INDIRECT(ADDRESS(ROW(),2)))

Rule #2 avoids formatting if Date in column B is less than or equal to the date in Column A  (i.e., "on time"), no formatting, stops evaluation if true

  • =INDIRECT(ADDRESS(ROW(),2))-INDIRECT(ADDRESS(ROW(), 1))<=0

Rule #3 applies the formatting of choice, e.g. "light red background", if NOTES are blank for something that's "late".

  • =ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN())))

This should properly apply absolutely zero formatting.

Cause the repro:

Copy the dates in column D to column B.

Expected Results:

The formatting causes one, and only one, cell to have the conditional formatting applied.

Actual Results:

  • The formatting is not immediately visually updated
  • The formatting is updated if the file is saved
  • The formatting is updated if the entire column containing the formatting is selected and then deselected (for that column)
  • The formatting is not updated if the particular cell requiring update is selected/deselected

Accordingly, it seems the issue is that the areas that require the formatting update are not properly getting redrawn in all instances.

100% repro with my existing file.

 


Registered Patent Agent | LCA - Patents | Microsoft
|
Answer
Answer
I have tried the CTRL+ALT+SHIFT+F9 key combo on an "unfixed" sheet and it works great.

Here is also another suggestion I've found online, this works equally well in Excel 2007 & 2010

-------------------------------------------------------------------------------------------------------
Solution for the auto refresh problem with conditional formatting in Excel 2007
Koen replied to Paul Strike
12-Feb-10 12:17 PM

1. Just set up your conditional formatting in the cell's you want.
2. Make sure you activate the "Developer" tab in the menu.
3. Do this by clicking on the Office button, click on Excel options and in the "Popular" section check the box ""Show developer tab in the Ribbon".
4. Click on the Developer menu and go to the Visual basic view.
5. Click on the menu View and select "properties Window". In that list you can find the "EnableFormatConditionsCalculation".
6. Set this value to "True" and the auto refresh will work.
7. Perform this action for all tabs in your sheet.
-------------------------------------------------------------------------------------------------------
http://www.eggheadcafe.com/community/excel/66/10059693/conditional-formatting-in-2007.aspx

33 people found this reply helpful

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated August 23, 2021 Views 39,319 Applies to: