Pivot table background color when collapsing some rows

[Thomas Kuriakose]

Dear Sirs,

On collapsing some rows in a pivot table the format of the empty cells does not match with the pivot table format.

Is there a way to retain the background in the same color as the pivot table on collapsing row data.



Thank you so much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

I have no idea , but my thinking would be that the boundary of the pivot table extends only till the Grand Total row / column , beyond which is the normal worksheet area.

Thus , the area you have indicated in your post could not be colored the same way as your pivot table ; it would be colored the way the rest of the worksheet is colored.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you for this information.

I selected the whole worksheet and set the fill color value as grey, removed the grid lines and expected the extended rows to have the grey color, but it becomes white when the rows are collapsed.

The file I have is greater than 2 MB.

I will try to reduce the size and send the same for your perusal.

Thanks once again,

with regards,
thomas

 

[NARAYANK991]

Hi Thomas ,

I have seen the issue. I think the only solution is to use VBA , on the lines of :

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
            Range("A3:D20").Interior.Color = RGB(200, 200, 200)
End Sub

Alter the specified range to suit.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

This worked perfectly.

Thank you so much for the solution provided.

with regards,
thomas