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