Pivot table custom formatting of group levels
 

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?

Pivot table custom formatting of group levels
 

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?




.

Pivot table custom formatting of group levels
 

Hi Mike,

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

Danny.


"MikeW" schreef in bericht
...
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?




.