Pivot Table Help (Excel 2000 SR1)

Topic

New Reply

How can I add formatted lines to pivot table main data. I can add lines in through formatting, however, once the data is refreshed the formatting disappears. I have also tried using the canned format options for pivot tables but none of them contain lines. Please see attached example. The data containing numbers is what I would like to add lines to. This would make the table more user friendly to read.

Reply | Quote

Replies

Yes, refreshing a pivot table can be downright frustrating after you’ve spent some time formatting it. The only way to insure you get your formatting the way you want is to create a macro that formats it after a refresh.

I do a lot of work with pivot tables, and I can tell you from experience that if you do go the macro route, you should copy the code to your worksheet (in the VBA editor) to use as a CHANGE event-driven macro. That way, if you (or anyone) uses the pivot table to select different options, the event code will immediately run and reformat the table.

Here is some code that I use in one of my tables to format every other row in gray shading.

1. Range(“A3”).Select
2. Range(Selection, Selection.End(xlToRight)).Select
3. Selection.Offset(1, 0).Select
4. Range(Selection, Selection.End(xlDown)).Select
5. Selection.FormatConditions.Delete
6. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= “=MOD(ROW(),2)=0”
7. Selection.FormatConditions(1).Interior.ColorIndex = 15
8. Range(“A1”).Select

Here’s what it does:

Line 1 selects cell A3, which is where my pivot table begins.
Line 2 selects all the populated cells in row 3. (This row contains the table’s column headers.)
Line 3 changes the selection to row 4, as I don’t want to reformat the column headings.
Line 4 selects all the populated cells in the columns (completing the selection of the pivot table).
Line 5 deletes any current conditional formatting within the table.
Line 6 applies the formula that selects every other row in the selection.
Line 7 applies the light gray shading to the selected alternate rows.
Line 8 leaves A1 as the active cell.

Hope this helps.

Regards,

Reply | Quote