Formula for a fill color help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bryan J - 04 Jan 2007 18:52 GMT

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

--
Bryan J

Reply to this Message

John Bundy - 05 Jan 2007 01:54 GMT

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

Signature

--
-John
Please rate when your question is answered to help us and others know what
is helpful.

> I can't figure out how to make a formula that will recognize a color as
> a value. More specifically; If b1 is made to be green (color index 4) I
[quoted text clipped - 13 lines]
>
> Bryan

Reply to this Message

Max - 05 Jan 2007 01:56 GMT

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
.. "
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I can't figure out how to make a formula that will recognize a color as
> a value. More specifically; If b1 is made to be green (color index 4) I
[quoted text clipped - 13 lines]
>
> Bryan

Reply to this Message

Gord Dibben - 05 Jan 2007 03:08 GMT

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

>I can't figure out how to make a formula that will recognize a color as
>a value. More specifically; If b1 is made to be green (color index 4) I
[quoted text clipped - 13 lines]
>
>Bryan

Reply to this Message