CODEWITHSUNDEEP
vbaexcelpivotpivot-table
Ariel
Ariel

Reputation: 978

Excel VBA Pivot Table Fill Header

In excel vba I am creating a pivot table and want to fill the header (first two rows) and bottom line (1 row) blue with white font.

The below code fills in the interior of the first data set row rather than the header.

With ActiveSheet.PivotTables(1).TableRange1
 .Cells.Borders.LineStyle = xlContinuous
 .Range("A3:I4").Interior.ColorIndex = 49
 .Range("A13:I13").Interior.ColorIndex = 49
End With

Is there a way to dynamically reference the header and the last line of the pivot table? It would be better to reference "pivot table header" than specific ranges incase values of the report change.

Upvotes: 0

Views: 3614

Answers (3)

WdW
WdW

Reputation: 1

The code presented in the thread so far does work, but will have to be rerun in future: Any time the number of rows or columns in the pivot changes, you may sit with coloured rows or columns where there is no pivot, or coloured cells in the wrong part of the pivot.

I suggest you look at the built in pivot styles of Excel, pick the one closest to what you need, and use code like this to modify the header row(s) and the grand total row. There is no need to manipulate ranges of cells, because next time you might want to turn off the total row (for argument's sake) but the range will still be coloured.

I recorded a macro and removed all redundant lines of code to help you see what changes. I also remove one useful row (.TintAndShade = ) and replaced it with a "ColorIndex = " statement in order to force the shade of blue you seem to prefer.

After running this, you should have the same style available to all other (existing and new) pivots in the same workbook. You could even make it default. No need to run code again to format when your pivot [refreshed] covers a different range of cells.

You can pick another style one from your own palette and use your own custom name for the copy, as long as you find and replace all relevant strings in the sample code below or record your own actions:

Sub ColourPivotStyleForThisBook()

    'duplicate blue/medium pivot style from template
    ActiveWorkbook.TableStyles("PivotStyleMedium2").Duplicate ("PivotStyleMedium2v2" _
        )
    With ActiveWorkbook.TableStyles("PivotStyleMedium2v2")
        .ShowAsAvailablePivotTableStyle = True
        .ShowAsAvailableTableStyle = False
        .ShowAsAvailableSlicerStyle = False
        .ShowAsAvailableTimelineStyle = False
    End With

    'now modify header's style (fill) (font colour is white/automatic already)
    With ActiveWorkbook.TableStyles("PivotStyleMedium2v2").TableStyleElements( _
        xlHeaderRow).Interior
        'only one/two lines are changing the colours
'        .TintAndShade = -0.249977111117893
        .ColorIndex = 49
    End With

    'now modify grand total row's style (fill) (font colour is white/automatic already)
    With ActiveWorkbook.TableStyles("PivotStyleMedium2v2").TableStyleElements( _
        xlTotalRow).Interior
        'only one/two lines are changing the colours
'        .TintAndShade = -0.249946592608417
        .ColorIndex = 49
    End With

End Sub

If the result you get is not what you're after, simply select part of a pivot in the workbook, tap the Design tab of the PivotTable Tools ribbon, right-click on the one your code has created (it should be in the Custom section at the top), and click Delete. Hope this helps.

(You can even use code like the above to customise PivotTable styles in other workbooks that don't have PivotTables yet.)

Upvotes: 0

Dirk Reichel
Dirk Reichel

Reputation: 7979

you can simply use this:

With ActiveSheet.PivotTables("PivotTable1")
  Debug.Print "full headers at: " & Intersect(.Parent.Range(.TableRange1.Row & ":" & .DataBodyRange.Row - 1), .TableRange1).Address
  Debug.Print "last row-range at: " & .TableRange1.Rows(1).Offset(.TableRange1.Rows.Count - 1).Address
End With

but looking at your sub, the easiest way would be to change:

.Range("A3:I4").Interior.ColorIndex = 49
.Range("A13:I13").Interior.ColorIndex = 49

to:

Union(.Rows("1:2"), .Rows(.Rows.Count)).Interior.ColorIndex = 49

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

There is a dynamic way to address the header rows and the last row:

Dim pvtFirstRow, pvtLastRow     As Integer

pvtFirstRow = ActiveSheet.PivotTables(1).TableRange1.row + x ' modify x to the first row where your Pivot data starts
pvtLastRow = ActiveSheet.PivotTables(1).TableRange1.Cells(ActiveSheet.PivotTables(1).TableRange1.Cells.count).row

Upvotes: 1

Related Questions