1004 Error on .Apply Line While Sorting By Color-CodePudding

I'm trying to sort a given range by cell color.

Getting "1004 - Application Defined or Object Defined" Error on the ".Apply" Line

Dim ColRng As Range
Set ColRng = Sheets("Outstanding").Range(Cells(FEnd   2, 1), Cells(ColEnd, 53))
Sheets("Outstanding").AutoFilterMode = False
Range(Cells(FEnd   2, 1), Cells(ColEnd, 53)).Activate
Selection.AutoFilter
Range(Cells(FEnd   2, 1), Cells(ColEnd, 53)).Select
ActiveWorkbook.Worksheets("Outstanding").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Outstanding").AutoFilter.Sort.SortFields.Add(Range( _
    Cells(FEnd   2, 1), Cells(ColEnd, 53)), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB( _
    255, 0, 0)
With ActiveWorkbook.Worksheets("Outstanding").AutoFilter.Sort
    .SetRange ColRng
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

The code initially worked when I used only one cell instead of range but it jumbled all the other columns.

Do I have to add something to qualify this range?

CodePudding user response：

You need to specifiy the range using .SetRange where you want to apply the sort. For example

With ActiveWorkbook.Worksheets("Out").AutoFilter.Sort
    .SetRange Rng '<~~ Set the range here
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Replace Rng with the relevant range.

If you are applying the sort to Range(Cells(FEnd 2, 1), Cells(ColEnd, 53)) then use that range there.

Tip: Your cell ranges are not fully qualified. It is advisable to fully qualify them to avoid errors as mentioned in Why does Range work, but not Cells?