Filtering Pivot Table to fill non-formatted cells with color.

[Diesel24]

Hi All,

I am sure that this is an easy fix for those that know what they are doing (unlike me).

I have a background color surrounding a pivot table in Excel, but when I filter out column entries, this leaves blank non-formatted cells in the pivot from the filtering. What I am wanting to do is fill this blank range with the surrounding color so no non-formatted cells are with the original range? I would like this to work automatically on the filtering without using a button.

Can anyone help me out with this?

I appreciate your expertise.

Regards,

Diesel24

 

[Worf]

Hi

I understood you want to format cells beneath the filtered table:

' sheet module
Dim ad, pt As PivotTable, pad$

Private Sub Worksheet_Activate()
Set pt = Me.PivotTables(1)
Application.EnableEvents = 0
pt.ClearAllFilters
Application.EnableEvents = 1
pad = pt.TableRange1.Address
ad = Split(pad, "$")                                ' unfiltered table
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim r As Range, s$, v
If Len(pad) = 0 Then
    MsgBox "Leave and return to this sheet.", 64, "Activation code required"
    Exit Sub
End If
Select Case pad = Target.TableRange1.Address        ' is the table filtered?
    Case False                                      ' yes
        v = Split(Target.TableRange1.Address, "$")
        s = "$" & ad(1) & "$" & CStr((CInt(v(4)) + 1)) & ":$" & ad(3) & "$" & ad(4)
        Set r = Me.Range(s)                         ' range to format
        r.Interior.ThemeColor = xlThemeColorAccent6
        r.Interior.TintAndShade = 0.8
    Case True                                       ' no extra format, only table style
        Set r = Me.Range(pad)
        r.Interior.Pattern = xlNone
        r.Interior.TintAndShade = 0
End Select
End Sub

 

[Diesel24]

Thank you!

 

[Worf]

You are welcome!