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.
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.
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.