Sort by Color in Excel 2003

May 18th, 2010 by datapig Leave a reply »

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.

Advertisement

23 comments

  1. sam says:

    1.Select Cell B1
    2.Define a Name (CTRL+F3) called ColorNum
    =GET.CELL(63,OFFSET(Sheet2!B1,0,-1))
    or
    =GET.CELL(63,Sheet2!A1)

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

  2. datapig says:

    Very cool Sam.

  3. vinu says:

    Hi Sam,

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

  4. Excel User says:

    Great tip datapig! Works well on my spreadsheets.

  5. thanks for this. got it to work. thanks again.

  6. bzbElder says:

    Thanks! Works like a charm!

  7. Forhad says:

    Goooooooooooooooooooooood

  8. Chrystal says:

    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?

  9. Ibrahim says:

    Thank you man, really helpful..

  10. candy says:

    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?

  11. Michael says:

    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?

  12. datapig says:

    Michael:
    Include this line as the first line in your function
    Application.Volatile

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

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

    This will force the function to fire when Excel recalcs.

  13. snehil says:

    that’s of a great help. thank you

  14. Chandrima says:

    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.

  15. Ava says:

    You saved my day with this! Thank you so much!

  16. Harvi says:

    wow didnt think this would work but it so did!!!!! THANK YOU!!!

  17. Matthew Fox says:

    Perfectly explained. Thank you!

  18. Nick Slater says:

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

  19. Nick Slater says:

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

    Application.Volatile

    Thanks for this !

  20. Kelly says:

    Hi Nick– I’m still getting the NAME? issue. How did you solve it?

  21. Roger Widdowson says:

    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!

  22. Heather says:

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

Leave a Reply

Powered by sweet Captcha