Refreshing data in pivot table removes color rules in conditional formatting

Question

I am trying to implement persistent conditional formatting of colors on specific rows of a pivot table. These rules work fine until I refresh the data. Every time the data refreshes, the color formatting disappears. I have ensured that the box is checked to persist formatting after data refresh: 

Conditional formatting before data refresh: 

Availability!$A$6:$AK$6

Conditional formatting after data refresh:

Availability!$AG$6:$AK$6,Availability!$A$6

Any idea what could be going on here? I am using Excel for Mac version 15.39.

Thank you!

Answer 1

Hello,

I don't know if Excel for Mac has that feature, but in Excel for Windows, conditional formats on pivot table values have a setting for applying the rule to a pivot table. Edit the rule and at the top of the rule window see if you can spot these "Apply Rule To" options.

If it's not there, then Mac may not have that yet.

Answer 2

Unfortunately, pivot tables have their own formatting, which will overwrite your formatting until you specifically tell it otherwise.

I think the simplest solution is a very simple macro, assuming you are ok with re-entering the rules for your conditional formatting.

We are not going to program a macro in VBA, just use the macro recorder.  First, hit the record macro button, then refresh your table, put in your conditional formatting, and hit stop recording.

Now any time you want to refresh, use your macro and it will re-apply the formatting afterwards.

Answer 3

Thank you both for your solutions!

I was actually able to resolve the issue by only selecting the specific cells that I want to perform color formatting on instead of selecting the entire row.