Colors as text

[Dan]

Hi,
I understand that each fill color is assigned a number in Excel. I need to
be able to have that number populate a cell, depending on the color of the
cell, so that I can filter to cells with that color. I have been looking
for a function to do this, but can't seem to find anything. I think I may
need a macro, but I am not sure.
For example, if A1 is highlighted bright yellow, I would like A1 to display
the number that is assigned for that color in Excel.

Thanks much,
DPR

 

[Myrna Larson]

Yes, you need 2 things: 1) that the color is not applied by Conditional
Formatting, and 2) a macro

Function InteriorColor(Rng As Range) As Long
InteriorColor - Rng.Cells(1).Interior.ColorIndex
End Function

 

[Gord Dibben]

Dan

See Chip Pearson's site for CellColorIndex UDF.

http://www.cpearson.com/excel/colors.htm

You will need a helper column to enter the function then sort on that column.


Gord Dibben Excel MVP

 

[Bob Phillips]

Hi Myrna,

typo alert

Function InteriorColor(Rng As Range) As Long
InteriorColor = Rng.Cells(1).Interior.ColorIndex
End Function

probably worth telling the OP that they would use it in an adjacent cell on
the worksheet, such as

=InteriorColor(A1)

in B1

--

HTH

RP
(remove nothere from the email address if mailing direct)