VBA Begginer - Set multiple PivotItems in a range

47 Views Asked by At

I'm trying to set a range so I can automatically add background color to specific cells. I'm writing a code, that is not completed, but I'm stuck with something.

I can color all single cells that has value and from just one kind of "type"... I have multiple "types" and I wanted to reference them all!!

My code is like this:

Dim ws As Worksheet
Dim pt As PivotTable
Dim rng1 As Range
Dim rng2 As Range
Dim rng As Range

Set ws = ActiveSheet
Set pt = ws.PivotTables("PivotTable14")
Set rng1 = pt.DataBodyRange
Set rng2 = pt.PivotFields("Type").PivotItems("Residential").DataRange

For Each rng In Intersect(rng1, rng2)
If rng.Value > 0 Then
rng.Interior.Color = vbYellow
End If
Next

How can I reference all PivotItems inside PivotFields "Type"???

I tried something like:

Set rng2 = pt.PivotFields("Type").PivotItems("(All)").DataRange

But that didn't work.

0

There are 0 best solutions below