Compile Error: Expected End With

Hi,

I have been recording 3 separate Marco’s in excel and now would like them to become one.

The separate Marco’s are:-
1) To format a header, and copy an “If” statement in to cell K4
2) To fill the copied information in K4 down to a dynamic range
3) To conditional format the rows that have "Yes" in the column K.

The script is below, but I get the error "Compile Error: Expected End With"
I have tried many things but I can't seem to join these Marcos together.

I hope someone can help me with this, as this s is driving me crazy

Thanks in advance
Rob

Sub Needs_Confirmation()
'
' Needs_Confirmation Macro
'

'
    Range("K3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799951170384838
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "Needs Confirmations?"
    With ActiveCell.Characters(Start:=1, Length:=20).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleSingle
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Columns("K:K").Select
    Columns("K:K").EntireColumn.AutoFit
    Range("K4").Select
    Sheets("Settlements Sheet").Select
    Range("P1").Select
    Sheets("1800").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(C[-5]=""Commitment Fee Internal"",""Yes"",IF(C[-5]=""Cross Currency Swap"",""Yes"",IF(C[-5]=""Fixed Loan Mortgage"",""Yes"",IF(C[-5]=""Floating Loan"",""Yes"",IF(C[-5]=""NDF"",""YES"",IF(C[-5]=""NDS"",""YES"",IF(C[-5]=""Interest Rate Swap"",""YES"",""NO"")))))))"

'
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveSheet
    .Range("K4").AutoFill Destination:=.Range("K4:K" & LastRow)



'
    Range("A4:K814").Select
    ActiveWindow.LargeScroll Down:=-20
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$K4=""YES"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Rob

It's hard to tell where the problem is without seeing the code in tags.

Could you edit and add them?

Rob

You can (should?) add code tags so that it makes the code easier to read/copy.

The tags will preserve any indentation and will make the code stand out from the rest of the post.

To add tags, select the code and press the # button in the toolbar above the reply box.


PS Try sticking End With just before End Sub.

Rob

I might have missed something - it is hard to read the code without the tags.

PS You can edit your original post to add the code tags.

Rob

That does help, the answer is still the same though - all you need to do is add End With just before End Sub.

Option Explicit

Sub Needs_Confirmation()
Dim LastRow As Long

    Range("K3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799951170384838
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "Needs Confirmations?"
    With ActiveCell.Characters(Start:=1, Length:=20).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleSingle
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Columns("K:K").Select
    Columns("K:K").EntireColumn.AutoFit
    Range("K4").Select
    Sheets("Settlements Sheet").Select
    Range("P1").Select
    Sheets("1800").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(C[-5]=""Commitment Fee Internal"",""Yes"",IF(C[-5]=""Cross Currency Swap"",""Yes"",IF(C[-5]=""Fixed Loan Mortgage"",""Yes"",IF(C[-5]=""Floating Loan"",""Yes"",IF(C[-5]=""NDF"",""YES"",IF(C[-5]=""NDS"",""YES"",IF(C[-5]=""Interest Rate Swap"",""YES"",""NO"")))))))"

    '
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    With ActiveSheet
        .Range("K4").AutoFill Destination:=.Range("K4:K" & LastRow)

        Range("A4:K814").Select
        ActiveWindow.LargeScroll Down:=-20
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$K4=""YES"""
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End With

End Sub