# Thread: Sort by colors (2003)

1. ## Sort by colors (2003)

Hi all,
Is it possible to sort by colors in Excel. Excel Help wasn't too helpful I'm afraid. I picked up the following code on the web, but I am not sure it is safe to use.

Bill

'Ranks a list of Colors so they can be sorted
'''''''''''''''''''''''''''''''''''''''''''''

Dim i As Integer
Dim ICol1 As Integer
Dim ICol2 As Integer

i = 1
ICol2 = -1
ColorRank = 0

'Loop until match is found
Do Until ICol1 = ICol2
'Replace "Font" with Font to sort by Font color
ICol1 = ColorOrder(i, 1).Font.ColorIndex
ICol2 = LookCell.Font.ColorIndex
If i = ColorOrder.Rows.Count + 1 Then
'No Match found place in Text
ColorRank = "No colour match!!!"
Exit Do
End If
'Pass the Row number of the colour match
ColorRank = i
i = i + 1
Loop
End Function

2. ## Re: Sort by colors (2003)

This is only part of a custom function from Sort by Color In Excel. Excel User Defined Function. You'll need to copy the entire function into a module.
You'll need to create a small table in your worksheet that lists color indexes in the desired order, say P1:P10 if you want to use 10 colors.
If the cells you want to sort are in A1:A100, enter the following formula in B1:
<code>
=ColorRank(\$P\$1:\$P\$10,B1)
</code>
and fill down to B100. You can then sort A1:B100 on column B.

3. ## Re: Sort by colors (2003)

While it is true that each workbook can have its own color pallet, sometimes it's nice to know what the current color values are, for the workbook you are in. Here is a link to a neat user defined function that tells you the Color Index number and shows its current color: http://www.vbaexpress.com/kb/getarticle.php?kb_id=206

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•