MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sort by colour

Posted by Mo on April 24, 2001 11:10 AM

I am trying to sort some data using colour as the criterion. Anyone know the macro?
Thanks in advance

Posted by Kevin James on April 24, 2001 11:35 AM

Sorting and subtotalling by interior fill color - Annette 22:13:32 02/26/01

She asked the same question.

Posted by Mo on April 24, 2001 3:48 PM

Hi Kevin, its not the same question, unfortunately.
Thanks for trying to help anyway

Posted by Kevin James on April 24, 2001 11:12 PM

Well, now that i went back and read more closely Annette's posting, I guess I have to eat dirt (or at least Humble pie).

I launched a search via Yahoo for all sites offering Excel help. I literally spent an hour reading dozens of pages. (Your question made me curious.) Guess what, except to get a VBA pgmr to help, there is no built in function. I'd guess that Dave Hawley, being the generous guy that he is, might write you a free routine if you ask him "pretty please."

Other than that, if you'd care to tell me what the different color cells contain, or what the logic is behind the color scheme, I'd be willing to suggest some means of doing the same thing using a formula based on your data.

Posted by Dave Hawley on April 25, 2001 2:17 AM

Ok, so the gauntlet has been tossed down !

I did write a macro to this this some time ago, but I cannot remember where or when :o(

What I have come up with is a more generic solution. What you need to do is place the code in a module, then in some cells put the colours in their sort order, lets say Cells A1:A10. Let's say your colour list you want to sort is in H2:H100. in a cell to the RIGHT of H2 put this formula:


The absoluting of A1:A10 is very important. As is the Relative reference of H2. Copy this down to row 100 and it will place a number from 1 to 10 in the cells based on the Interior colour of cell H Whatever. Now copy and PasteSpecial as Values over the top of the formulas and then sort by that Column.


OzGrid Business Applications

Posted by Dave Hawley on April 25, 2001 2:19 AM

..And now the CODE! :o)

Function ColourRank(ColorOrder As Range, LookCell As Range)
'Wriiten by OzGrid Business Applications

'Will list a referenced cell fom 1 to X

Dim i As Integer
Dim ICol1 As Integer
Dim ICol2 As Integer
'Force recalculation
'set variables
i = 1
ICol2 = -1
ColourRank = 0
'Loop until match is found
Do Until ICol1 = ICol2

ICol1 = ColorOrder(i, 1).Interior.ColorIndex
ICol2 = LookCell.Interior.ColorIndex

If i = ColorOrder.Rows.Count + 1 Then
'No Match found place in Text
ColourRank = "No colour match Mo!!!"
Exit Do
End If
'Pass the Row number of the colour match
ColourRank = i
i = i + 1


End Function


OzGrid Business Applications

Posted by Mo on April 25, 2001 11:00 AM

Thanks Dave & Kevin