# Microsoft Excel - value of a color

**URL:** <https://boards.straightdope.com/t/microsoft-excel-value-of-a-color/441785>\
**Category:** Factual Questions\
**Created:** [March 18, 2008, 3:06pm UTC](https://boards.straightdope.com/t/microsoft-excel-value-of-a-color/441785 "2008-03-18T15:06:05Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tastes\_of\_Chocolate](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tastes_of_chocolate/32/3232_2.png) [@Tastes\_of\_Chocolate](https://boards.straightdope.com/u/Tastes_of_Chocolate)\
**Post date:** [March 18, 2008, 3:06pm UTC](https://boards.straightdope.com/t/microsoft-excel-value-of-a-color/441785/1 "2008-03-18T15:06:05Z")

</div>

Is there a function in Excel which will return some kind of alphanumeric value based on the color of a cell? Specifically, I’m looking for the background color, although if that exists I’d guess there is one to specify font color, size, style etc.

I’m looking for a way to sort a spreadsheet based on the color that my customer decided to assign to some lines. If the above function exists, I’d just create a new column, have the program fill in the color code and sort on that.

Thanks.

---

<div class="post-metadata">

**Author:** ![Nametag](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/nametag/32/406_2.png) [@Nametag](https://boards.straightdope.com/u/Nametag)\
**Post date:** [March 18, 2008, 3:14pm UTC](https://boards.straightdope.com/t/microsoft-excel-value-of-a-color/441785/2 "2008-03-18T15:14:15Z")

</div>

[QUOTE=Tastes of Chocolate]  
Is there a function in Excel which will return some kind of alphanumeric value based on the color of a cell? Specifically, I’m looking for the background color, although if that exists I’d guess there is one to specify font color, size, style etc.

I’m looking for a way to sort a spreadsheet based on the color that my customer decided to assign to some lines. If the above function exists, I’d just create a new column, have the program fill in the color code and sort on that.

Thanks.  
[/QUOTE]

Google for “excel color index”; you will find a large amount of information on how to do that, but you’ll need to decide which approach is right for you.

Short answer: no, but you can make one.

---

<div class="post-metadata">

**Author:** ![Sal\_Ammoniac](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@Sal\_Ammoniac](https://boards.straightdope.com/u/Sal_Ammoniac)\
**Post date:** [March 18, 2008, 7:53pm UTC](https://boards.straightdope.com/t/microsoft-excel-value-of-a-color/441785/3 "2008-03-18T19:53:42Z")

</div>

Years ago, some kind person on these here boards told me how to do exactly that. Here goes:

"Okay, this is easy – don’t let the number of steps scare you off.  
• In your Excel file, choose Tools-\>Macro-\>Visual Basic Editor.  
• The VB editor will start.  
• Now select Insert-\>Module.  
• You’ll have a blank editing area, and the first line of VB code will already be there, and probably be “Option Explicit.”  
• Below that text, paste (having copied) the following text:

Function GetMyColor(theRange As Range) As Long  
GetMyColor = theRange.Interior.ColorIndex  
End Function

• For colored text, it looks like changing “Interior” to “Font” would do the trick.  
• Hit save and exit.

Now the “GetMyColor” function is just a normal Excel function. Here’s how you can sort by color:

• Say all of your color-coded things are in column A, and that column B is available.  
• Just put =GetMyColor(A1) into B1. You can copy and paste or fill-down column B to put the same function in every other cell in the column.  
• Now you’ll have just a bunch of numbers representing the color codes of the cells.  
• You can now sort on this column."

I can say that I’ve done this successfully, but since I don’t know VB Script, I can’t give you any coding advice.