Cell formatting not updating
Different types of cell formats will display your data differently in Excel 2013.Changing the format of a cell to best suit the data contained within it is a simple way to ensure that your data displays correctly.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. 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.
BUT for some of the cells I need to set custom format to ;;; (to hide the text form users.. I have a drop down box to choose if dates are shown i months, weeks or days.
Problem is that when the drop down box is changed the conditional formatting is NOT updating.
After changing the drop down box I have to preform 2 updates of random cells before the formatting is updates (not 1 but 2 changes which is very strange...) The following fixes do NOT work: 1) Enable Format Conditions Calculations is set TRUE in the VBA properties for the sheet (has no affect on above issue) 2) The sheet is of cause set to auto calculate.
This is very annoying that the pivot table can’t retain the formatting.
Today, I will talk about an easy trick to deal with this task.