SAMCRO2014
Board Regular
- Joined
- Sep 3, 2015
- Messages
- 158
I am trying to write VBA coding to add conditional formatting to a pivot table per tab using a loop. The problem I am having is the loop is looking for a pivot table name (like PivotTable22) and tab name (like AD - Audit). Is there a way to have the macro add the conditional formatting by not needing the pivot table name and sheet name.
Here is my coding with notes
'Set up "Comments" header and conditional formatting for each manager pivot table using a loop
For Each ws In ActiveWorkbook.Worksheets
With ws
'Autofit columns
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
'Change column I width to 40
Columns("I:I").ColumnWidth = 40
'Format I4:I5 to match H4:H5
Range("H4:H5").Select
Selection.Copy
Range("I4:I5").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("J14").Select
'Add conditional formatting to highlight variance less than -300 or great then 300
ActiveSheet.PivotTables("PivotTable22").PivotSelect _
"'Sum of Variance' AD_Clerical", 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
.PatternColor = 13551615
.ColorIndex = xlAutomatic
.PatternTintAndShade = 0
End With
'Add Comments header
Range("I5").Select
ActiveCell.FormulaR1C1 = "Comments"
Range("L5").Select
Next ws
Here is my coding with notes
'Set up "Comments" header and conditional formatting for each manager pivot table using a loop
For Each ws In ActiveWorkbook.Worksheets
With ws
'Autofit columns
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
'Change column I width to 40
Columns("I:I").ColumnWidth = 40
'Format I4:I5 to match H4:H5
Range("H4:H5").Select
Selection.Copy
Range("I4:I5").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("J14").Select
'Add conditional formatting to highlight variance less than -300 or great then 300
ActiveSheet.PivotTables("PivotTable22").PivotSelect _
"'Sum of Variance' AD_Clerical", 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
.PatternColor = 13551615
.ColorIndex = xlAutomatic
.PatternTintAndShade = 0
End With
'Add Comments header
Range("I5").Select
ActiveCell.FormulaR1C1 = "Comments"
Range("L5").Select
Next ws