?
Solved

When is excel conditional formatting refreshed ?

Posted on 2013-10-24
6
Medium Priority
?
2,096 Views
Last Modified: 2013-10-28
I have a worksheet, with the calculation is set to manual, where the on one sheet the formatting of one range of cells is dependant on the boolean value of another cell on the same sheet. I am using Excel 2010.

I then created a second range on a second sheet that also depends on the same control cell.

If I change the value of the control cell then the cells on sheet 1 do not change their formatting. If I switch to sheet 2 then the sheet 2 range has changed there formatting (based on the control cell in sheet1). If I switch back to sheet 1 then the range on that sheet has now had it's formatting updated also.

I can repeat these actions over and over again with the same result.

To put is simply : the refresh of the conditional formatting is only performed the the worksheet changes when the calculation mode is set to manual. If the calculation mode is set to auto then of course all formatting is updated immediately.

My question : Is this normal expected behaviour ?

I have attached a sample file to demonstrate this point. The control is the yellow cell on sheet 1.
Conditional-Formatting-refresh.xlsx
0
Comment
Question by:AL_XResearch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 400 total points
ID: 39596942
Theoretically, conditional formatting needs to perform calculations too, so that it stands to reason that Manual Calc settings will not change instantly the settings of your sheet.  The fact that jumping from one sheet and back does the changes is probably more of a quirk or glitch than by design.  I tested yours in 2013 version and it reacts the same way as you mention.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39596947
It doesn't seem unusual to me. When the range onscreen has to be redrawn, the CF is re-evaluated - you'll see the same behaviour if you simply scroll up and down on Sheet1 so that the range is off- then on-screen again. I think that's probably more efficient than re-evaluating all of  the CF values for every calculation.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39597121
So basically the conditional formatting will refresh immediately but
The redraw is not performed unless the 'display' status of the cells change i.e. scrolling to show the cell or switching sheet
If the change in displayed formatting depends on a calculation then if the calc mode is set to manual then of course the formatting will not change.

This would then mean that the reevaluation of the CF is separate to the calc it just happens that in normal Excel use these two coincide and appear to be the same.

So how can you force a refresh from VBA without a calc ? Show and hide the sheet or it's cells ?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1600 total points
ID: 39597231
Just using:
application.ScreenUpdating = True

Open in new window

should do it.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39597522
rorya: Thanks so much ! I have used 'ScreenUpdating' for years to make sure my VBA actions did not show on the screen - I never knew you could use it to force a refresh.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39597528
NB_VC:
The fact that jumping from one sheet and back does the changes is probably more of a quirk or glitch than by design
That is basically what I was trying to confrm.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question