Loop through columns of excel pivot with vba

3.3k views Asked by At

I am trying to loop through the columns of a pivottable to add conditional formatting. I cant figure out how to select the data of a column. I only able to select the columnheader. In the following code I left the conditional formatting part away to reduce complexity.

Sub Format_Pivot_Columns()
  Set pt = ActiveSheet.PivotTables("piv_scrapedata")
  For Each ptFld In pt.ColumnFields
    ptFld.DataRange.Select
    Selection.Interior.Color = vbYellow
  Next ptFld
End Sub

Any idea? Thanks!

1

There are 1 answers

0
Christian On

I ended up with the following code which works like a charm. Thanks for the inspiration!

Sub Format_Pivot_Columns()
 Set pt = ActiveSheet.PivotTables("piv_scrapedata")
 For Each ptFld In pt.DataBodyRange.Columns
  ptFld.Select
  Selection.Interior.Color = vbYellow
 Next ptFld
End Sub