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.