Microsoft Office 2000/Visual Basic Programmer's Guide

Formatting PivotTable Data

To format an area of a PivotTable report, you can return a range that represents that particular area, and apply formatting to the range. For example, if you’re displaying sales information in the data area of a PivotTable report, you probably want to format those cells with a Currency format. You can use the DataBodyRange property of a PivotTable object to return a reference to the data range. The following line of code sets the NumberFormatLocal property for a PivotTable’s data range.

ThisWorkbook.ActiveSheet.PivotTables(1).DataBodyRange.NumberFormatLocal = _
   "$#,##0.00"

Some other useful properties that return PivotTable ranges are the RowRange, ColumnRange, PageRange, TableRange1 and TableRange2 properties.