Trying to give color to column field headers in pivot table with vba

Question

Im trying to give white color to the font titles of the columns in my pivot table.

I tried doing it like this:

' Format column field headers
For Each pvtField In pvtTable.ColumnFields
    With pvtField.LabelRange
        .Interior.Color = RGB(0, 0, 77) ' Dark Blue
        .Font.Color = RGB(255, 255, 255) ' White
        .Font.Bold = True
    End With
Next pvtField

But it didnt work, then I tried a more manual way, like this:

' Column headers are the fith row in TableRange2
Set headerRow = pvtTable.TableRange2.Rows(5)
With headerRow
    .Interior.Color = RGB(0, 0, 77) ' Dark Blue
    .Font.Color = RGB(255, 255, 255) ' White
    '.Font.ColorIndex = 2 ' White ' White
    .Font.Bold = True
End With

With this second code I manged to get the .Interior.Color = RGB(0, 0, 77) ' Dark Blue correct, but not the font color white.

Any ideas?

Answer 1

• Using TableRange2.Rows(5) to locate the the header is not reliable. It depends on how many page fields on pvt table.

• TableRange1 represents the range containing the entire PivotTable range, but doesn't include page fields.

Microsoft documentation:

PivotTable.TableRange1 property (Excel)

Microsoft documentation:

Application.Union method (Excel)

Sub HighLightPvtHeader()
    Dim pvtTable As PivotTable, pvtField As PivotField
    Dim headerRow As Range
    Set pvtTable = Sheet1.PivotTables(1)
    With pvtTable.TableRange1.Rows(2)
        Set headerRow = .Resize(, .Columns.Count - 1).Offset(, 1) ' modify as needed
    End With
    For Each pvtField In pvtTable.ColumnFields
        Set headerRow = Union(headerRow, pvtField.LabelRange)
    Next pvtField
    With headerRow
        .Interior.Color = RGB(0, 0, 77) ' Dark Blue
        .Font.Color = vbWhite
        .Font.Bold = True
    End With
End Sub