Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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???

Re: Colour sorting

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


Re: Colour sorting

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


Re: Colour sorting

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


Re: Colour sorting

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.