Pivot table custom formatting of group levels

[DannyG]

Is it possible to assign a different formatting for each RowField in a Pivot
table without using the built-in autoformats in Excel?
f.i. One wants a report with the revenues per city, per state. Now it is
desired to make the row with names of the state and its revenues pink and
the row with city names and revenues blue.

I have investigated the interfaces in the Excel type library and searched de
MSDN documentation on it, but all I can achieve is to format the pivot table
regions through commands such as:
With ActiveSheet.PivotTables(1)
.HasAutoFormat = False
.TableRange1.Style = "Test"
.DataBodyRange.Style = "Test2"
.RowRange.Style = "RowStyle"
.ColumnRange.Style = "ColumnStyle"
End With

Although these properties seem to be promising, it just formats all the
rowfields and not the data, neither is it possible to apply different
formatting to group levels. You end up with a pivot table that is pink on
the left and white on the right. This does not help to clarify the groupings
in the report.

So I start to wonder if it is possible to reproduce a report with a style
and formatting similar to the built-in autoformats in Excel?

 

[MikeW]

Hi Danny

Check out the PivotSelect method in Excel VBA help. Turn
on the recorder and do it manually. It will give you a
framework to modify.

You'll need code something like this:

ActiveSheet.PivotTables("PivotTable2").PivotSelect "CITY
[All]", xlDataOnly
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveSheet.PivotTables
("PivotTable2").PivotSelect "STATE[All;Total] DOLLARS", _
xlDataAndLabel
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Mike

 

[DannyG]

Hi Mike,

Thanks for your response. Your suggestion seems to open up a lot of new
possibilities.

Danny.

Hi Danny

Check out the PivotSelect method in Excel VBA help. Turn
on the recorder and do it manually. It will give you a
framework to modify.

You'll need code something like this:

ActiveSheet.PivotTables("PivotTable2").PivotSelect "CITY
[All]", xlDataOnly
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveSheet.PivotTables
("PivotTable2").PivotSelect "STATE[All;Total] DOLLARS", _
xlDataAndLabel
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Mike

-----Original Message-----
Is it possible to assign a different formatting for each RowField in a Pivot
table without using the built-in autoformats in Excel?
f.i. One wants a report with the revenues per city, per state. Now it is
desired to make the row with names of the state and its revenues pink and
the row with city names and revenues blue.

I have investigated the interfaces in the Excel type library and searched de
MSDN documentation on it, but all I can achieve is to format the pivot table
regions through commands such as:
With ActiveSheet.PivotTables(1)
.HasAutoFormat = False
.TableRange1.Style = "Test"
.DataBodyRange.Style = "Test2"
.RowRange.Style = "RowStyle"
.ColumnRange.Style = "ColumnStyle"
End With

Although these properties seem to be promising, it just formats all the
rowfields and not the data, neither is it possible to apply different
formatting to group levels. You end up with a pivot table that is pink on
the left and white on the right. This does not help to clarify the groupings
in the report.

So I start to wonder if it is possible to reproduce a report with a style
and formatting similar to the built-in autoformats in Excel?




.