Sort by Color in Excel 2003

Every now and then, you encounter a table where color is supposed to mean something. In this table, each color represents a certain attribute (red = below target, yellow = in danger). If you’re thinking that tagging records with a color is stupid, I agree. But we live in a world full of goofballs that think it’s clever to do things like this.


The problem here is that tagging records with just a color makes it difficult to find and consolidate the like-colors (especially in Excel 2003 where, unlike Excel 2007, there are no built-in tools that allow you to easily sort by color).


But with a little VBA, there is a way to sort by color in Excel 2003.


Step 1 – Create Your Custom Functions

  • Press Alt-F11 on your keyboard to get to the Visual Basic Editor.
  • Go to the menu and select Insert -> Module.
  • Paste this bit of code into the empty module.

Function GetBackgroundColor(MyRange As Range)
GetBackgroundColor = MyRange.Interior.ColorIndex
End Function

Function GetFontColor(MyRange As Range)
GetFontColor = MyRange.Font.ColorIndex
End Function



Step 2 – Apply your Custom functions

  • Create a new column and call it Color Index
  • In your new column, enter the Get Color function needed. In this case, I need to get the color index for the cell background color, so I will use the GetBackgroundColor function.



Step 3 – Sort by the Color Index Column

  • Sort your data table by the newly created Color Index column.



There you have it – Sorting by color in Excel 2003.  As I mentioned before, Excel 2007 has a better (built-in) solution. To learn how to sort by color in Excel 2007, check out Debra’s Post.


I’d like to think that not many of you will need this Excel 2003 tip, but I know that some companies will keep you their folks on Excel 2003 for another 2-3 years.

24 thoughts on “Sort by Color in Excel 2003

  1. sam

    1.Select Cell B1
    2.Define a Name (CTRL+F3) called ColorNum

    3.Select a cell and type = ColorNum
    This will return the fill color of the adjacent cell

  2. vinu

    Hi Sam,

    could you please brief me how it works internally your formula…what does it mean “get.cell”

  3. Chrystal

    I can’t seem to get this to work if the color I’m “indexing” is done through conditional formatting. They’re all reading -4142 even though they conditional format of one of the cells is orange. Any ideas?

  4. candy

    I can get the cell in the color index column to give me a number by using the GetBackgroundColor function but it only does it for the one cell, how do I get it to work in the entire column?

  5. Michael

    Works fine on my tables in Excel 2003, thanks for that.
    But when I change the background color of a row, the value is not updated automatically.
    What can I do?

  6. datapig

    Include this line as the first line in your function

    Function GetBackgroundColor(MyRange As Range)
    GetBackgroundColor = MyRange.Interior.ColorIndex
    End Function

    Function GetFontColor(MyRange As Range)
    GetFontColor = MyRange.Font.ColorIndex
    End Function

    This will force the function to fire when Excel recalcs.

  7. Chandrima

    This is working for me when I am adding color to the cells normally, But when I am adding color by conditinal formatting, It is showing -4142 even for colored ones.

    It will really great if somebody help me how I get get color index for the cells/range where color is asigned by conditional formatting.

  8. Nick Slater

    Help ! I’m getting #NAME? error here, have double checked spelling and location of vb code. Am running excel 2003… Any assistance gratefully recieved !

  9. Nick Slater

    OK – problem solved – works a treat – and recommend datapig’s suggestion to include


    Thanks for this !

  10. Roger Widdowson

    People like you datapig are priceless!

    You just made my life a whole lot easier with this little VBA code.

    I wish I understood it though as you cannot grow as an individual without having a complete understanding. That’s something for mty later years when I have time I think!

  11. Heather

    I remembered seeing this wehn you first posted it and TODAY, I actually needed it! Thank you for making my life easier – now can you get people to stop using shaded cells/fonts to indicate something that could be easily shown in another column?? :)

  12. Thank you!

    Thank you so much for this! I tried looking at stuff like this all over the internet and this was the only thing to work for me! However; I do like to color code things. It’s visually appealing and people take less time to recognize a color than a word.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>