VBA to add Conditional Formatting to Pivot Tables

[SAMCRO2014]

I don't know if this is even possible. I am trying to create a loop which will go through all the worksheets in a workbook and apply conditional formatting to a column of a pivot table in that worksheet.

This is my coding which does not work:

'Add conditional formatting to all pivot tables
For Each ws In ActiveWorkbook.Worksheets
With ws.PivotTables.PivotSelect "'Sum of Variance'", _
xlDataAndLabel , True
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
Formula1:="=-300", Formula2:="=300"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Next ws

Can you see the error in my coding? Is this even possible with pivot tables? Any guidance would be greatly appreciated.

 

[Worf]

An example:

Sub main()
Dim pt As PivotTable, i%, s$, rr As Range, irow%, Lrow%
s = "Sum of fiscal year"                ' desired column
For Each pt In ActiveSheet.PivotTables
    For i = 1 To pt.DataFields.Count
        If pt.DataFields(i).Caption = s Then
            Set rr = pt.TableRange2.Find(s, pt.TableRange2.Cells(1, 1), xlValues, 1)
            irow = Left(Split(pt.DataBodyRange.Address, "$")(2), 1)
            Lrow = Split(pt.DataBodyRange.Address, "$")(4)
            PTColumn Range(Cells(irow, rr.Column), Cells(Lrow, rr.Column))
        End If
Next i, pt
End Sub

Sub PTColumn(r As Range)        ' range to be formatted
Application.CutCopyMode = 0
r.FormatConditions.AddColorScale ColorScaleType:=3
r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
r.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
With r.FormatConditions(1).ColorScaleCriteria(1).FormatColor
    .Color = 7039480
    .TintAndShade = 0
End With
r.FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
r.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With r.FormatConditions(1).ColorScaleCriteria(2).FormatColor
    .Color = 8711170
    .TintAndShade = 0
End With
r.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
With r.FormatConditions(1).ColorScaleCriteria(3).FormatColor
    .Color = 8109670
    .TintAndShade = 0
End With
r.FormatConditions(1).ScopeType = 2
End Sub

 

[SAMCRO2014]

Thank you so much!!!!