Pivot table conditional formatting changing on collapsing pivot table

Hi,

I have a pivot table, to which I have added a calculated column. On this column i have added 5 conditional formats to colour code the cells to be different colours based on "cell value between" condition. The Applies to reads "=$D$3:$D$500".

It looks great on the pivot table UNTIL i collapse the pivot table or refresh the data.

Then the conditional format disappears and the Applies To reads =$D$3,$D$45:$D$500.

How can i keep the conditional formatting when I

1. refresh the data
2. collapse the data?

Thanks in advance for your help

Instead of applying the rules to a range of cells they need to be applied to all cells showing sum of field 4 values.
I have changed the rule for cell value >=1 as an example.
The other rules will need to be deleted and then re applied using the second option under apply rules to in the dialog box.
This linked tutorial may help you better understand: https://www.excelcampus.com/pivot-ta...atting%20to%20
Let us know if you have any questions.