In Excel 2003 (at least), if you apply the formatting within the pivot table,
the formatting follows the size of the pivot table. If this doesn't work in
your workbook you should be able to solve it by making the formatting
conditional on whether or not there is data in the cells.

/Marcus


" wrote:

I have an excel file that updates a pivot table when I run a macro. The
size of the re-generated pivot table (in the # of rows) can vary
depending on the source data. When I print the pivot table, I don't
want to print pages and pages of blank rows underneath. I also don't
want to have to resize my print area each time. In the past I have
avoided this problem by clearing the print area instead of setting a
print area. However, if I try to format a large # of rows [background
color, text alignment etc] to accomodate a wide ranging pivot table
size, excel then assumes that you want ALL of these rows printed
regardless of the size of the pivot table, even though the rows after
the table are blank. Is their any way to avoid this?