Lost "Applied to" Conditional formatting with a Pivot Table

[DannyV]

Hello all,

I am trying to highlight a pivot table row to draw attention to the current week.

My formula works correctly to highlight the current week, however every time the table is updated or if I apply a filter, I lose the formatting on all cells except the first cell in the row.

Can anyone tell me what I'mdoing wrong?

Here is my formula for the target area for the formatting:

=$B$6:$G$56

Thanks in advance.

 

[chirayu]

Please upload sample file

 

[Chihiro]

With PivotTable, you will lose CF if data range is updated or structure changes.

CF can be retained, but with limited capabilities in newer versions (2013 & 2016 I believe). By selecting 2 options other than "Selected cells".


However, this may not meet your need as it applies to all columns that meet criteria. In that instance, you will need to use VBA to apply CF. See links for example codes.

http://peltiertech.com/pivot-table-conditional-formatting-with-vba/
http://yoursumbuddy.com/re-apply-excel-pivot-table-conditional-formatting/

 

[DannyV]

Thanks @Chihiro, the two websites' contents look very promising. I use quite a lot of VBA, but I wanted to be sure that there wasn't something simple that could be done to fix my problem.

You're very helpful.