Formula for a fill color help

[Bryan J]

I can't figure out how to make a formula that will recognize a color a
a value. More specifically; If b1 is made to be green (color index 4)
would like c1 to insert the letter Y. I am I'm looking for an I
statement so I can use it throughout the whole workbook.

The formula if I understand it should kind of look like (in C1):

=If(b1=colorindex4,"Y","")

I'm looking to put a "Y" in c1 if b1 is colored in green.

I would really like to also know how to insert a fill color in an
formula!

Thank you for any of your help and time,

Brya

 

[John Bundy]

You can add color and perform tasks with them through code but not with a
standard function. You can however create a UDF.

 

[Max]

Try this sample from my archives:
http://www.savefile.com/files/378485
Count n Sum Cells By FillColor_BobPhillips_ColorIndex_UDF.xls
(nicely rendered, full details)

The sample file contains an implementation of Bob Phillips' ColorIndex
Function from his "Processing Coloured Cells" page at:
http://www.xldynamic.com/source/xld.ColourCounter.html
and some examples on how to use the UDF in Excel

In the sample file, albeit its not illustrated therein,
you would be able to use IF's along these lines:
=IF(ColorIndex(D2)=4,"Y","")
(above would be as per your intents expressed in the post)

Note: As clarified in Bob's page under "Constraints" section, you would
need to press F9 to force calculate as, quote: " .. change a cell's
colour or the text colour does not trigger the Excel calculate event.
This is not a shortcoming of the UDF, but of Excel's calculation event
... "

 

[Gord Dibben]

Bryan

Excel cannot do this without VBA code of some type.

See Chip Pearson's site for UDF's for working with colors.

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

With Chip's CellColorIndex UDF your formual would look like

=IF(Cellcolorindex(B1)=4,"Y","")


Gord Dibben MS Excel MVP