When is excel conditional formatting refreshed ?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rory Archibald
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.
ASKER
So basically the conditional formatting will refresh immediately but
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
NB_VC:
The fact that jumping from one sheet and back does the changes is probably more of a quirk or glitch than by designThat is basically what I was trying to confrm.