使用vba对excel pivot列进行循环

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.

我尝试遍历数据透视表的列，以添加条件格式。我不知道如何选择一列的数据。我只能选择columnheader。在下面的代码中，我省略了条件格式部分以减少复杂性。

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 个解决方案

#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