Sort by colour

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

She asked the same question.

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

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.

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:

=ColourRank($A$1:$A$10,H2)

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.

Dave

OzGrid Business Applications

..And now the CODE! :o)

Function ColourRank(ColorOrder As Range, LookCell As Range)
'Wriiten by OzGrid Business Applications
'www.ozgid.com

'''''''''''''''''''''''''''''''''''''''
'Will list a referenced cell fom 1 to X
'''''''''''''''''''''''''''''''''''''''

Dim i As Integer
Dim ICol1 As Integer
Dim ICol2 As Integer
'Force recalculation
Application.Volatile
'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

Loop

End Function

Dave

OzGrid Business Applications

Thanks Dave & Kevin