Code to add gridlines to Excel Pivot Tables

P

Pat Dools

This was more cumbersome than I realized (at least with Excel 2003). I found
this code on another forum and it works like a charm:

Here's the link to the post >>
http://lounge.windowssecrets.com/index.php?showtopic=737024

Here's the post itself:

You can create code that will format the pivot table automatically each time
it is updated:
- Right-click the sheet tab of the worksheet that contains the pivot table.
- Copy/paste the following code into the worksheet module:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
With Target.TableRange1.Borders
..LineStyle = xlContinuous
..ColorIndex = xlColorIndexAutomatic
..Weight = xlThin
End With
End Sub

- Modify as needed; you can change the style, color and width of the
borders, for example.
- The above code will apply borders to the entire pivot table except the
page field (if present).
- If you want to include the page field, use TableRange2 instead of
TableRange1.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top