Adding Conditional Formatting to Pivot tables using a Loop VBA

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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top