Use the 'Use a Formula to determine which cells to format'.
Preserve formatting after refreshing pivot table To keep the cell formatting when you refresh your pivot table, please do with following steps: 1.
Select any cell in your pivot table, and right click. Then choose Pivot Table Options from the context menu, see screenshot: 3.
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.
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.Therefore the value is forced to update the formatting Application.Enable Events = False Range("rg Calander Range") = Range("rg Calander Range") Application.3) All forms of calculations on the worksheet does not update the formatting I have solved this by writing below function in VBA..however I think this is a bad solution as I wanted a workbook without code the user are not able to undo any changes in the workbook when I'm using the Worksheet_Calculate function: Private Sub Worksheet_Calculate() 'The conditional formatting is not updating when value of dropdown box is changed...Sometimes, you may remove the check mark of the Preserve cell formatting on update item in the pivot table options accidentally.