Loop through columns of excel pivot with vba

Question

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!

I tried to duplicate your question but can't. I suggest you step through the code and set a Watch in Visual Basic on pt and inspect which fields it has (I didn't have Columnfields, but you may see it and see what fields ColumnFields has, and so on). — Paul Ogilvie
– Paul Ogilvie, Commented Jun 13, 2015 at 16:47

Christian · Accepted Answer · 2015-06-21 19:47:58Z

1

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

answered Jun 21, 2015 at 19:47

Christian

211 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Loop through columns of excel pivot with vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related