CODEWITHSUNDEEP
excelvbaformattingpivot-tablecell
ChrisD
ChrisD

Reputation: 319

EXCEL: Format cells based on style of adjacent Pivot Table

I'm trying to develop a report which is largely Pivot Table but with some adjacent cells for some other (non pivot-table-able) text values.

Each week the Pivot Table will be refreshed and rows with subheadings etc will move depending on the underlying data.

Every time this happens I have to manually format those adjacent cells to match the format of the pivot table.

Does anyone know how to make the cells inherit the Pivot Table's formatting 'style', please?

Here's an illustration of what I'm trying to achieve: enter image description here

Thanks in advance!

Upvotes: 1

Views: 927

Answers (2)

Olly
Olly

Reputation: 7891

Try something like this, in the Workbook module:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim c As Range
    With Target.DataBodyRange
        Set c = .Columns(.Columns.Count)
    End With
    With c.End(xlToRight)
        .CurrentRegion.ClearFormats
        c.Copy
        .PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End With
End Sub

Upvotes: 2

Marco Vos
Marco Vos

Reputation: 2968

You could create a named list of the main categories in the first column of the pivottable ({ACC, ACT, BAS, etc}). Presuming [ACC] is in cell A5, you use the following formula to conditionally format the non-pivot tabel:

=IFERROR(MATCH(A5,Named_List,0),0)>0

Upvotes: 1

Related Questions