SAMCRO2014
Board Regular
- Joined
- Sep 3, 2015
- Messages
- 158
I am trying to add some formatting to all worksheets in a workbook but I keep getting compile error: "Expected End With". I cannot seem to find what I am doing wrong.
Can you see where the error is?
Code:
'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
Columns("H:H").Select
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
'Remove conditional formatting from "Sum of Variance"
Selection.FormatConditions(1).StopIfTrue = False
Range("H5").Select
Selection.FormatConditions.Delete
Range("N9").Select
'Add Comments header
Range("I5").Select
ActiveCell.FormulaR1C1 = "Comments"
Range("L5").Select
End With
Next ws
Last edited by a moderator: