asked on

Compile Error

Okay, all I'm using is the Macro recorder to do some basic filtering. I tried updating it with the suggestions above and it gives me a "Compile error: Expected End With" error. I can write SQL queries but this new Macro language is driving me nuts. Below is the code.. please help.

Option Explicit

Sub Formats()
'
' Formats Macro
'

'
With Sheets("Wages")
Columns("C:C").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("H:H").Select
Selection.NumberFormat = "m/d/yyyy"
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=42370"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("K:K").Select
Selection.NumberFormat = "00000000000"
ActiveWindow.SmallScroll ToRight:=4
Columns("N:N").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("O:O").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("P:P").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=11
Columns("AC:AC").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("F:AC").Select
Range("AC1").Activate
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

K Tin

ASKER

Thanks guys! Just needed that extra End With.... I'm a dope.

Norie

Give this a try, it fixes the error and also gets rid of all the unneeded Select/Selection stuff.

Sub Formats()
'
' Formats Macro
'

'
    With Sheets("Wages")
    
        With .Columns("C:C")
            .FormatConditions.AddUniqueValues
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).DupeUnique = xlDuplicate
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 15773696
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
        
        With .Columns("H:H")
            .NumberFormat = "m/d/yyyy"
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
                                  Formula1:="=42370"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Font
                .Color = -16383844
                .TintAndShade = 0
            End With
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13551615
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With

        .Columns("K:K").NumberFormat = "00000000000"

        With .Columns("N:N")
            .FormatConditions.AddUniqueValues
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).DupeUnique = xlDuplicate
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 15773696
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
        
        With .Columns("O:O")
            .FormatConditions.AddUniqueValues
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).DupeUnique = xlDuplicate
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 15773696
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
        
        With .Columns("P:P")
            .FormatConditions.AddUniqueValues
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).DupeUnique = xlDuplicate
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 15773696
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
        
        .Columns("AC:AC").NumberFormat = "m/d/yyyy"
        
        With .Columns("F:AC")
            .HorizontalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    End With

End Sub

Open in new window

Martin Liss

I'm a dope.

No, you're just inexperienced. Please don't forget to select one or more of our responses as the answer.

Martin Liss

I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015

Roy Cox

Pleased to help