MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Colour sorting


Posted by Caroline on August 05, 2001 7:56 PM

HI! I have a big list of data that varies in (4)colours, and would like to sort it by colour. Can this be done, if so HOW???


Posted by Cory on August 06, 2001 7:01 AM

The only way I can think of is to assign a number to each of the four colors (Red is 1, blue is 2, etc...) and enter this code in a column next to the data you want to sort. Then you can sort by this number (which now represents your colors), and hide the column when you're done.

If you need more than that, just ask...

Cory

Posted by Kurt on August 06, 2001 8:52 AM

I think this can be done using the CELL() function. I had a similar problem and I had to format the cells using different decimal place. The formula would detect what decimal place was give to a particular cell and add or ignore it accordingly.

For example, cells that have blue text, could be formated with two decimal places (0.00) while green text could be given three decimal places (0.000) the CELL function could then be used to give a True/False reading and from there you can run your analysis.

Good luck

Posted by lenze on August 06, 2001 11:46 AM

This doesn't cover sorting, but might give you an idea.
http://www.cpearson.com/excel/colors.htm

Posted by faster on August 06, 2001 2:33 PM

this should do it

Sub CheckColor()

'Create a column called ColorSort
'this example it is column "B"
'so B1 = ColorSort

Range("B2").Select
Dim MyEnd
MyEnd = Cells.SpecialCells(xlCellTypeLastCell).Row + 1

Do While Selection.Row < MyEnd
ActiveCell = Selection.Interior.ColorIndex
Selection.Offset(1, 0).Select
Loop

Range("A1").Select

End Sub