Linking cell shading

Posted by Tony on January 31, 2001 6:47 PM

Hi All,

Gotta curly one here....

Cell A1 is shaded red, Cell A2 is shaded yellow, I need a formula or conditional format in Cell A10 that says: If Cell A1 is red colour A10 red etc etc.

I really need this as a conditional format so I can enter formulas in the cells as normal..

Thanks for your help in advance.

Tony

Posted by cpod on January 31, 2001 8:09 PM

First insert a named range, call it ShadingA1 and in Refers To put:

=GET.CELL(38,$A$1)

Then select cell A10 and in conditional formating enter the formula:

=ShadingA1 = 3

and select red shading. You must do a full calc, Control+Alt+F9 to effect the change in A10 if the shading in A1 changes.

Posted by Tony on January 31, 2001 8:31 PM

Hi CPOD,

Can you tell me what the 38 refers to in the refer formula ?

Cheers,

Tony

Posted by cpod on February 01, 2001 6:51 AM

38 is just the code number that refers to the cell's shading color.

Posted by Mark W. on February 01, 2001 6:56 AM

Tony, GET.CELL() is an old Excel Macro 4.0 function that
cannot be used directly in a worksheet cell. 'cpod' is
cleverly circumventing this limitation by defining a name
that refers to this function. '38' is one of 66 values that
specifies the type of information you're seeking. The
definition for 38 is: "Shade foreground color as a number in
the range 1 to 56. If color is automatic, returns 0."

I would advise against using color in this manner. What's
'blue' to one person may be 'green' to another. If you
need to track an additional attribute you should do it with
a value is a separate cell.