Hi Nadia,

I would use Conditional Formatting for PivotTables to apply the background colours so that it automatically updates with change to the PivotTable.

Mynda

Hello Mynda,

Thanks for your response. I tried to change Pivot table formatting but still unable to arrive at the result i want. Could you please take a look at the file when you get a chance? i attached the example 

Thanks a lot

Nadia 

Hi Nadia,

You haven't tried to apply any conditional formatting to that file as I recommended and linked to above, so I'm not sure what you've tried.

1. Select cell A1

2. Then click on the triangle in the top left of the grid between the row number 1 and column letter A to select the whole sheet.

3. Home tab > Conditional Formatting > New Rule > 'Use a formula to dtermine which cells to format'

4. In the Rule Description field enter the formula below (note that the cell reference to A1 is relative, not absolute):

=ISBLANK(A1) 

5. Click on the 'Format' button > set the cell fill to the light blue colour

6. Click OK and OK again.

Mynda

Hello Mynda,

Sorry, what i initially tried is to format Pivot table itself using "format only cells that contain" Blanks, so this didnt work (i also tried that contains zero)

I followed your instructions and used =ISBLANK formula for the entire sheet, not just the Pivot table area. Filtered Pivot table for 2 states and the background was blue. yay! However, when i cleared the filter and selected 2 new states, formatting rule no longer worked and the background was white again

Is there a way for the Worksheet to remember the formatting? 

Thanks a lot for your help, it is VERY much appreciated!

Hi Nadia,

Looks like the CF 'Applies to' range is being disrupted when the PivotTable changes shape. Not much you can do about this, it's just a "feature" of CF. You have two options:

1. abandon the blue background. It's unneccesary and only ads to the file size for little/no benefit. 

2. Find a VBA solution that automatically applies the blue fill after the PT refresh/update. Downside, file must be saved as a macro enabled workbook with the extension .xlsm and when opened, Macros will need to be enabled by the user.

Mynda

You are the best! Thanks a lot