SAMCRO2014
Board Regular
- Joined
- Sep 3, 2015
- Messages
- 158
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.
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.