Pivot table structure – Colorized PivotCellTypes

When developing code for handling Pivot tables it is helpful to always know how the pivot table in their different formats is organized. Helpful for this is the PivotCelltype property. The demo routine colorize the first pivot table on a worksheet per these PivotCellTypes.

'------------------------------------------------------------------------------- 
' Procedure : Colorize_PivotCellTypes 
' Author    : Hartmut Gruenhagen 
' Date      : 04-Feb-14 
' Purpose   : Colorize a pivot table based on the pivotcelltypes 
'------------------------------------------------------------------------------- 
' 
Sub Colorize_PivotCellTypes()
'--- All cells in a Pivot table can be one of the XlPivotCellType constants.---- 
   Dim pivCellType As XlPivotCellType
   Dim pCell As PivotCell
   Dim pt As PivotTable
   Dim cell As Range
   If ActiveSheet.PivotTables.Count = 0 Then
      MsgBox "Select a worksheet with a Pivot table and try again."
   Else
      With ActiveSheet.PivotTables(1)
         .TableStyle2 = ""
         For Each cell In .TableRange2.Cells
            ' some cells above TableRange1 are not pivotcells 
            On Error Resume Next
            Set pCell = cell.PivotCell
            On Error GoTo 0
            If Not pCell Is Nothing Then
               pivCellType = pCell.PivotCellType
               Select Case pivCellType
               Case xlPivotCellValue
               ' Any cell in the data area (except a blank row).    0  grey 
                  Call ColorCell(cell, pivCellType + 2, 0.92)
               Case xlPivotCellPivotItem
               ' Pivotfield Cell in row/column area which isn't sub-or grand 
               ' total, custom subtotal, or blank line. 
                  Call ColorCell(cell, pivCellType + 2, 0.5)   '    1  beige 
               Case xlPivotCellSubtotal
               ' Cell in the row/column area which is a subtotal.   2 blue 
                  Call ColorCell(cell, pivCellType + 2, 0.7)
               Case xlPivotCellGrandTotal
               ' Cell in a row/column area which is a grand total.  3 light blue 
                  Call ColorCell(cell, pivCellType + 2, 0.5)
               Case xlPivotCellDataField
               ' A data field label (not the Values/Data button).   4 red 
                  Call ColorCell(cell, pivCellType + 2, 0.7)
               Case xlPivotCellPivotField
               ' The button for a field (not the Data button).      5 green 
                  Call ColorCell(cell, pivCellType + 2, 0.3)
               Case xlPivotCellPageFieldItem
               ' Cell that shows the selected item of a Page field. 6  violett 
                  Call ColorCell(cell, pivCellType + 2, 0.5)
               Case xlPivotCellCustomSubtotal
               ' Cell in row/column area that is a custom subtotal. 7 blue-mint 
                  Call ColorCell(cell, pivCellType + 2, 0.7)
               Case xlPivotCellDataPivotField
               ' The Values/Data title.                             8  orange 
                  Call ColorCell(cell, pivCellType + 2, 0.2)
               Case xlPivotCellBlankCell
               ' A structural blank cell in the PivotTable.    9  light violett 
                  Call ColorCell(cell, pivCellType + 2, 0.8)
               End Select
               Set pCell = Nothing
            End If
         Next cell
      End With
   End If
End Sub

Sub ColorCell(colorRng As Range, lngThemeColorIndex As Long, TintShade As Double)
   With colorRng.Interior
      .PatternColorIndex = xlAutomatic
      .ThemeColor = lngThemeColorIndex
      .TintAndShade = TintShade
   End With
End Sub