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

Please Login or Register  to view this content.

Originally Posted by jindon

Try this

Please Login or Register  to view this content.

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.