Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Thanked 0 Times in 0 Posts

    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.
    Advice please. Thanks.


    '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
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    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:
    and fill down to B100. You can then sort A1:B100 on column B.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2004
    Albuquerque, New Mexico, USA
    Thanked 1 Time in 1 Post

    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:

Posting Permissions

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