Formatting all worksheets in Workbook - Compile Error

[SAMCRO2014]

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.

'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

Can you see where the error is?

 

[RoryA]

No, because it's not in what you have posted.

 

[mumps]

Try this version (untested). When using "With...End With" you must use "dot notation".

For Each ws In ActiveWorkbook.Worksheets
    With ws
        'Autofit columns
        .Columns("A:E").AutoFit
        'Change column I width to 40
        .Columns("I:I").ColumnWidth = 40
        'Format I4:I5 to match H4:H5
        .Range("I4:I5").Value = .Range("H4:H5").Value
        '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").FormatConditions.Delete
        'Add Comments header
        .Range("I5") = "Comments"
    End With
Next ws

 

[SAMCRO2014]

Ok. This is the only additional coding I have added to this macro so I thought I the error had to be in there. It worked before I added in the coding. Looking closer it is giving me this error when End Sub is high lighted. The coding after the above is:

' Hide SPS, PPDR, Data, and RC 1203-All tabs

SPS.Activate
Range("H1").Select

Sheets(Array("SPS+", "PPDR", "Data", "RC 1203-All")).Select
Sheets("Data").Activate
ActiveWindow.SelectedSheets.Visible = False

'Mesage Box to let you know the macro has finished running
MsgBox "Done Like Dinner!!!!"

'Turn on printing and screen updating to speed up the macro
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.PrintCommunication = True

End Sub

I am stumped. Any suggestions?

 

[Fluff]

Can you please post the entire code?
When doing so please use code tags, the # icon in the reply window.

 

[SAMCRO2014]

Can you please post the entire code?
When doing so please use code tags, the # icon in the reply window.

I just figured it out!!!! Thanks anyways!!!

 

[Fluff]

Glad you sorted it & thanks for letting us know.

 

[mumps]

Please ignore...problem solved.