none
Excel 2010 Conditional Format Recalculation Not Working Correctly

    Question

  • I have a spreadsheet developed in Excel 2003 that is used for data entry.  There are hundreds of cells that change color (yellow to indicate that entry is OK in the cell, green to indicate that data has been entered, red or gray to indicate that data entry is not currently allowed in that cell).  The Conditional Format in a cell uses the contents "this cell" or the contents of "other cells" to determine what color the cell should be.

    All worked great in Excel 2003.

    In Excel 2010, if I modify a cell (which should cause other cells to change color), the recalculation of the color is not done correctly.  As an example, if an entry is changed that should cause 20 cells to change color, maybe 4 or 5 cells will change color.  The other cells will either maintain the same color or in some cases, a small part of the cell (usually a small bar at the top of the cell) will have changed to the correct color.  

    However (and this is important) if I page down and then page up, all of the cells will be shown at the correct color.

    So there seems to be an error in the recalculation process.

    Thanks for your help.

    • Moved by Sally Tang Monday, August 16, 2010 7:20 AM (From:Outlook IT Pro Discussions)
    Thursday, August 12, 2010 9:46 PM

Answers

  • There is a property to be set for this in each sheet

    Open the VBE and select the errant sheet from the object list

    Set the EnableFormatConditionsCalculation to True

    Put this in the Workbook Open event to apply the fix to all sheets

    For Each Sheet In ThisWorkbook.Sheets
        Sheet.EnableFormatConditionsCalculation = True
    Next

    HTH
    P

    • Proposed as answer by EmmaRoyd Wednesday, June 13, 2012 3:03 PM
    • Marked as answer by SteveMicroSoft Wednesday, June 13, 2012 5:54 PM
    Wednesday, June 13, 2012 3:02 PM

All replies

  • Hi,

     

    How many rules are you using for conditional formatting? Some rules may be override when there are several rules. Please select the cells and click Conditional Formatting, click Manage Rules.  

    If you use conditional rules that overlap, there's always the possibility of conflict. For example, one conditional formatting rule might apply a red background fill while another sets a yellow background fill. If both these rules affect the same cell, only one can win.

    In this situation, it all depends on the order in which Excel applies conditional formatting rules. If there's a conflict, rules that are applied later override the rules it applied earlier.

     

    FYI:

    http://answers.oreilly.com/topic/1687-how-to-use-conditional-formatting-in-excel/

    Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

     

    Best Regards,

     

    Sally Tang

     

    Monday, August 16, 2010 7:21 AM
  • Hi Sally,

    Thanks for your reply.

    I don't think there is a problem with the conditional formatting rules, for two reasons:

    1) The spreadsheet works great in Excel 2003, both under XP and Windows 7

    2) It is more of a display issue, not a calculation issue.  When I make a change to a cell which causes a change in conditional format, it doesn't display correctly.  But if I page down and the page up, causing no change in calculation but allowing the screen to repaint, the conditional formatting shows correctly.  

    Is there any method of uploading a JPEG to this forum?  If so, I can show you screen shots of what is happening.

    Thanks,

    Steve

    Sunday, August 22, 2010 1:51 PM
  • How can you say that this is a solution (green checkmark)? Steve's response makes it clear that your answer is not the solution, and marking it as such keeps the real answer from being discovered, and misleads the rest of us who are searching for solutions. Very frustrating and, I think, inappropriate, how easily one can mark one's own replies as answers, and stifle both the conversation and further research. It's like signing a problem ticket as "Resolved" because you can't be bothered any more. If it is not actually solved, leave it open.
    Monday, March 14, 2011 4:47 PM
  • Hi ghSea,

    It's been a while since I checked back here for any answers.  Thanks, I had not noticed that this issue had been wrongly marked as solved.  

    The problem still exists, thanks for your help.

    Steve

    Friday, July 15, 2011 2:59 PM
  • Some time ago I responded to post conditional formatting problems that post can be found here. The post was never really resolved as to OP of that thread never responded.

    As the OP of this post thought he may be having similar issues a comment he added to thread lead me to spending some time researching his issue, the one described in the first post.  The OP has kindly sent me a version of his file and I have carried out a number of tests to try to resolve the issue.

    The conditional formatting rules are not complex, although there are three rules on the cell.  One checks a value at the top of the column for TRUE or FALSE, One whether the current cell is empty (<>””), and the last if a cell in the column two to the right has the value of 1.

    As a result of changing the empty cell to an “X” or 1 the conditional formatting should then change  the current cell to green (rule 2) and other cells similarly formatted to red as this update would cause 1 to be the resulting value in the column two to the right (Rule 2).  If I concentrate on just the one column it will be easier, but there are other cells on the sheet that should update formats as well.

    The observation of the fault is putting an X in the top cell of a column of eight cells, causes the amended cell to change to green which is expected.  The next cell remains unaltered (but that is expected). The next cell turns red as expected. Another unchanged cell, again expected. On the fifth cell which should be red, red horizontal bar 2 pixels thick across the top of the cell. Cells six, seven and eight remain yellow but should be red.  PgDn/PgUp and the cells display correctly.

    Removing the X entered in the first cell does nothing until PgDn/PgUp and evertything is formatted correctly ie no conditional formats applied.

    Initially my thoughts where towards hardware differences as the original post states some machines worked and others failed.  So I used the file on a variety of platform combinations:

    • Windows 7 64bit and 32/bit, Windows XP and Snow Leopard OS X 10.6
    • With Office 2010 (.xlsm, .xlsx and compatibility mode .xls), 2007, 2003 and Mac 2008

    In all tests the VB code was not enabled or not present due to the file type used.

    These tests showed that the only combination to constantly fail to draw the screen correctly, involved Excel 2010.  All the versions listed drew the screen correctly as soon as the data had been entered.

    During the testing I became aware that in addition to the OPs observation that Page Down and Up would redraw the screen correctly, so does the use of Undo/Redo to change the value of the cells, eg enter X in cell gives incorrect presentation as described above, use Undo to remove the entry, incorrect formats removed, use Redo to put the X back and the screen draws without error.

    This lead me to think about calculation as I believe that Undo/Redo have little to burden on the calculation.  After changing calculation settings in 2010 I was unable to get a satisfactory result with correctly displayed cells when changing the cell contents.  After a bit of research looking for what had changed between 2007 and 2010 versions I came across this page in MSDN, in which I found a very useful command to force a full recalculation. After using CTRL+ALT+SHIFT+F9 the screen formatting is drawn correctly, unlike the standard recalculation with F9.

    Form here I was able to devise a solution to use this command in VBA so that the user of the sheet would be oblivious of the problem if using a 2010 version of Excel.  Unfortunately although the concept worked successfully a version which performed the calculation required only when one of the cells on the real spread sheet was amended proved too time consuming to implement.  As a result the coded solution is little more than the solution provided by the OP in his first post (PgDn then PgUp on the change of cells in the affected worksheet.)

    Although we have arrived at a solution that means the user of the file will be unaware of the issue described it still leaves the question open as to why Excel in particular 2010 versions cannot complete the process of formatting the sheet correctly, using conditional formatting, that earlier versions can.

    Is it because the new calculation engine is somehow terminating the processing of conditional formats when it has finished it’s smart recalculation?  Why does 2007 seem to not show the same symptoms as 2010?

    The code solution at present is to add the following code to the affected sheet in the workbook:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Application.Version = "14.0" Then
    
      Applcication.ScreenUpdating = False
      ActiveWindow.LargeScroll Down:=1
      ActiveWindow.LargeScroll Down:=-1
      Applcication.ScreenUpdating = TrueEnd If
    
    End Sub
    

     


    G North MCT
    Monday, July 25, 2011 8:21 PM
  • Tried to edit above but won't save code should read

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Application.Version = "14.0" Then
    
     Applcication.ScreenUpdating = False
     ActiveWindow.LargeScroll Down:=1
     ActiveWindow.LargeScroll Down:=-1
     Applcication.ScreenUpdating = True
    End If
    
    End Sub
    


    G North MCT
    Tuesday, July 26, 2011 6:46 AM
  • Over a year and still no solution. Great

    Think CF has been completely messed up in 2010. Got a couple of issues working with a file that needs to be 2003/2010 compatible and they all track back to this (although I will admit it has CF applied in thousands of cells):

    • Macro Performance is killed with CF applied when using selection.insert or selection.delete commands. Macro to copy info to new sheet and delete from original took 0.35sec in 2003, now takes 58.5secs. Same macro moving thousands of rows is going to really get on my nerves ...There's a new flag Sheets(#sheetID).EnableFormatConditionsCalculation = False - but that does precisely Jack. Preferred Solution: None
    • Opening a 2003 workbook in 2010 it reconstructs the CF in it's new object model. For the same basic file this has taken anywhere between 5min to ... well we gave up at 20 and killed the app. Preferred Solution - hang about in 2010 and save as native format. The delay of 2003 opening the 2010 version is about 3 secs, much better than 20 mins ;)

    Which causes:

    • After saving in 2010 forat none of my VBA buttons will work in 2003. Security process is preventing me accessing my code even though I just enabled macros ... Preferred Solution: Swap VBA buttons for Form Buttons - work just the same but don't bugger up in 2003.
    • 2003 compatability mode doesn't know 2003's validation rules. Yes I had a 2003 file, opened it in 2010, did nothing and clicked save ... all of a sudden the file isn't 2003 compatible???

    And that's in addition to the obligaritory "what did you do with my menu bar" question - someone who's been using Excel in anger for 15 years shouldn't have to spend 20 mins working out how to change the page margins in your latest release ... Proposed Solution: Use Ubit's Menu Creater to get them back!!!


    Oha nd I did have the problem above as well - will try your solution, thanks, but forcing a screen update after every change might affect my VBA code, Think I can modify it with a global variable though ;)
    • Edited by Chris Meacher Friday, September 16, 2011 3:49 PM Missed making post relevant ...
    Friday, September 16, 2011 3:47 PM
  • Hi Steve

    A new development.

    It appears that copying the sheet will somehow fix the issue.  I've tried it on PinMux and it appears to make a difference. At least the cells format correctly.

    I appreciate you may not be too happy with this solution as it will require you to work through all the formula that link to the old PinMux and point them at the new one to keep the whole workbook working correctly.

    If it is a problem in the sheet itself then that will explain why I could not reproduce the issue outside your file.

    Have a read here

    http://answers.microsoft.com/en-us/office/forum/office_2010-excel/conditional-formatting-not-updating-automatically/746bf300-1328-436f-87fd-47cbfe9378d0?page=1&tm=1318580084083#footer


    G North MCT
    • Proposed as answer by EmmaRoyd Wednesday, June 13, 2012 3:00 PM
    • Unproposed as answer by EmmaRoyd Wednesday, June 13, 2012 3:00 PM
    Friday, October 14, 2011 8:24 AM
  • There is a property to be set for this in each sheet

    Open the VBE and select the errant sheet from the object list

    Set the EnableFormatConditionsCalculation to True

    Put this in the Workbook Open event to apply the fix to all sheets

    For Each Sheet In ThisWorkbook.Sheets
        Sheet.EnableFormatConditionsCalculation = True
    Next

    HTH
    P

    • Proposed as answer by EmmaRoyd Wednesday, June 13, 2012 3:03 PM
    • Marked as answer by SteveMicroSoft Wednesday, June 13, 2012 5:54 PM
    Wednesday, June 13, 2012 3:02 PM
  • Hi,

    That appears to perfectly solve the problem on my Excel 2010 system.  

    I notice that back in September 2011 Chris Meacher mentioned that same parameter (EnableFormatConditionsCalculation), which he says is new for 2010, but he reported that enabling this parameter did not fix anything.  On my system, the functionality is now good.

    Thanks for pointing it out,

    Steve

    Wednesday, June 13, 2012 4:27 PM
  • Hi,

    "(EnableFormatConditionsCalculation), which he says is new for 2010"

    If this works in 2010 then thats great.

    What if a user opens the file in 2003 again.

    The functionality is not there so won't they get an error message?

    Would this work better in 2003 and in 2010 in the Workbook Open event?

    If Application.Version = "14.0" Then
    For Each Sheet In ThisWorkbook.Sheets
        Sheet.EnableFormatConditionsCalculation = True
    Next
    End If


    Just got a user with this issue so did a search of the web and found this forum - hence my late posting. :)
    • Edited by or-8 Tuesday, September 18, 2012 8:05 AM
    Tuesday, September 18, 2012 8:04 AM