Sort one column by multiple colors

[YasserKhalil]

Hell everyone

I have tried the following code to sort column B starting in B7 and the sort I desire based on 5 colors

Code: Select all

Sub Test()
    With Worksheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add(Range("B7"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176, 240)
        .SortFields.Add(Range("B7"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 255)
        .SortFields.Add(Range("B7"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 0)
        .SortFields.Add(Range("B7"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(252, 213, 180)
        .SortFields.Add(Range("B7"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 192, 0)
        .SetRange Range("A7:G" & Cells(Rows.Count, "B").End(xlUp).Row)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I have tested the code, no errors but I didn't get the cells ordered by colors as expected

[YasserKhalil]

Thanks a lot. It seems I have to determine the whole range not only the first cell
Now it worked when I set the whole range in SortFields lines