Sort range by alpha and then by font color with VBA

I have a named range "Trade" (first column of range is A) which I would like to sort first by alpha and then by
font color so that the black font items (ColorIndex = 1) will be on top and the pink font (ColorIndex = 7) items will be below.

Can anyone help me with this?

Originally Posted by AlphaFrog

Details are a bit vague, but try something like this...

Thanks Alpha for helping out!

Sorry that I wasn't clear enough.
Right now your code is sorting, but only alpha, despite adding that column B and checking for the font values.

I would like to sort by alpha AND by font colour.
Is there a way to do it without creating that column B?
I realize that it is deleted in the end.

Thanks again!

Try this

Option Explicit

Sub test()
    Dim a, i As Long, ii As Long, iii As Long
    Dim myColor As Long, n As Long, w, SL As Object
    Set SL = CreateObject("System.Collections.SortedList")
    With Range("myrange")   '<---- change here to real name
        a = .Value
        For i = 2 To UBound(a, 1)
            If Not SL.contains(a(i, 1)) Then
                Set SL(a(i, 1)) = CreateObject("System.Collections.SortedList")
            End If
            myColor = .Cells(i, 1).Font.ColorIndex
            If Not SL(a(i, 1)).contains(myColor) Then
                ReDim w(1 To UBound(a, 2), 1 To 1)
            Else
                w = SL(a(i, 1))(myColor)
                ReDim preservew(1 To UBound(a, 2), 1 To UBound(w, 2) + 1)
            End If
            For ii = 1 To UBound(a, 2)
                w(ii, UBound(w, 2)) = a(i, ii)
            Next
            SL(a(i, 1))(myColor) = w
        Next
        With .Offset(1)
            .ClearContents: .Font.ColorIndex = xlAutomatic: n = 1
            For i = 0 To SL.Count - 1
                For ii = 0 To SL.GetByIndex(i).Count - 1
                    With .Rows(n).Resize(UBound(SL.GetByIndex(i).GetByIndex(ii), 2))
                        .Value = Application.Index(SL.GetByIndex(i).GetByIndex(ii), 0, 0)
                        .Font.ColorIndex = SL.GetByIndex(i).GetKey(ii)
                    End With
                    n = n + UBound(SL.GetByIndex(i).GetByIndex(ii), 2)
                Next
            Next
        End With
    End With
End Sub

Originally Posted by jindon

Try this

Option Explicit

            For ii = 1 To UBound(a, 2)
                w(ii, UBound(w, 2)) = a(i, ii)
            Next
            
End Sub

Thanks very much jindon for helping out!
I really appreciate it.

I ran the code in debug mode and it kept cycling in a loop at the point I have indicated above. The loop would not end.

Can you upload a small sample workbook with before/after format?

Thanks for helping out Izandol and Jindon.
I am still using 2002.

Today I will consider making a big change in my workbook!
I think I will use two sheets instead of one for my calculations.

That way I can put my 'active trades' (black) on one page and my 'scenario trades' on a second page (pink).

By doing this, I wouldn't need to have two criteria for the sorts (alpha + color).
For now I will mark the thread solved.