Pivot Table Formatting - Excel 2003

[ajay_gajree]

Good morning,

I am having issues formatting a Pivot Table, I have formatted a table with borders, colours etc, but on the next day when new data is added and the table is refreshed the formatting does not carry through to the next day.

How can I format a Pivot Table so that the formatting is for all cells even when criteria is amended?

Thanks and regards

Ajay Gajree

 

[pbornemeier]

When a pivot table is refreshed, any manually applied formatting is removed.

You can use this code to select various parts of the Pivot Table.

Sub SelectPivotTableParts()
    Dim bHasPageRangeField As Boolean
    Dim bHasColumnRangeField As Boolean
    Dim bHasRowRangeField As Boolean
    Dim bHasDataRangeField As Boolean
 
    Dim lX As Long
 
    With ActiveSheet.PivotTables(1)
        For lX = 1 To .PivotFields.Count
            If .PivotFields(lX).Orientation = xlPageField Then bHasPageRangeField = True
            If .PivotFields(lX).Orientation = xlRowField Then bHasRowRangeField = True
            If .PivotFields(lX).Orientation = xlColumnField Then bHasColumnRangeField = True
            If .PivotFields(lX).Orientation = xlDataField Then bHasDataRangeField = True
        Next
 
        .TableRange1.Select
        MsgBox "TableRange1"
 
        .TableRange2.Select
        MsgBox "TableRange2"
 
        If bHasPageRangeField Then
            .PageRange.Select
            MsgBox "PageRange"
        End If
 
        If bHasRowRangeField Then
            .RowRange.Select
            MsgBox "RowRange"
        End If
 
        If bHasColumnRangeField Then
            .ColumnRange.Select
            MsgBox "ColumnRange"
        End If
 
        If bHasDataRangeField Then
            .DataBodyRange.Select
            MsgBox "DataBodyRange"
        End If
 
        If bHasDataRangeField Or bHasColumnRangeField Or bHasRowRangeField Then
            .DataLabelRange.Select
            MsgBox "DataLabelRange"
 
            For lX = 1 To ActiveSheet.PivotTables(1).VisibleFields.Count
 
                .VisibleFields(lX).DataRange.Select
                MsgBox "Visible Field " & lX & " (" & .VisibleFields(lX).Name & ") Data Range "
 
                .VisibleFields(lX).LabelRange.Select
                MsgBox "Visible Field " & lX & " (" & .VisibleFields(lX).Name & " Label Range "
 
            Next
        End If
 
    End With
End Sub

If you are using Excel 2007 or later, you can apply a format to the pivot table that will be maintained through pivot table updates.

Otherwise (or in addition) you can to adapt the above code to apply desired formatting when triggered by the worksheet PivotTableUpdate event.