Sort by Colour (color)???

[Peter Rouse]

I know this may sound a bit eccentric, but is it possible to sort a list by the fill colo(u)r of the cell?
Suffice to say, we have a humungeous list of units, which are classified only by the fill color. This originated as a legacy system and comes from worksheets sent to us by different groups, but all use this colour coding. The colour represents various company business units as well as locations.
Any ideas?
Thanks

Peter

 

[Mudface]

Take a look here: -

http://www.mvps.org/dmcritchie/excel/colorsort.htm

 

[lenze]

One way to do this is to create a column of the colorindex for your cells and then sort on these numbers. You can write a macro to assign the indexes. Something like

Sub ColorNameIndex()
Dim cel As Range
Dim MyValue As Integer
Dim myArea As Range
Set myArea = Selection
For Each cel In myArea
MyValue = cel.Interior.ColorIndex
cel.Offset(0, 1).Value = MyValue
Next
End Sub

By selecting a column, this macro will insert the color index in the next column. Adjust as needed

HTH