CODEWITHSUNDEEP
vbaexcelpivot-table
Christian
Christian

Reputation: 21

Loop through columns of excel pivot with vba

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!

Upvotes: 1

Views: 3585

Answers (1)

Christian
Christian

Reputation: 21

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

Upvotes: 1

Related Questions