Formula to identify colour of another cell

AFAIK, its not possible to do that with a formula,

how do you color the cell? manually or with Cond Format?

Manually

Try this;

1. Select B2

2. Insert|Name|Define....|Names in workbook: cCol|Refers to: =GET.CELL(63,!A2)|OK

3. Then I used the formula shown in B2 and copied down.

Of course this depends on just what green/red colours you use. To test, in B2 you could initially just put the formula
=cCol
For me that returned 35 in B2 and 3 when I copied it down to B3 and that is where thos numbers in my formula came from.

Edit: This may not update as readily as you might like but I think this could be addressed with some vba code but, unless somebody else steps in, it will have to wait a while because I have to log out for now.


</td></tr></table></td></tr></table>

Peter,

This =Get.Cell() function goes back years ago - MS created it obvioulsy to
satisfy certain requirements that couldn't be satified any other way (I guess).

The function has not been recognized since xl97 or before and there is no
2003, or 2007 equivalent. Can you shed further light on this function?

Thanks for your input!

Jim

Try this:

Start by creating a function to get the background color: Paste the following in the module of your workbook:


Then create a lookup range for all the colors you want to use. On the left column of your range write the number the ColorNumber funcion returns for that color. I got 3 for red, 14 for green and 1 for black so I put those values to the left column and wrote the color names to the right column & named that range (C2:D4) "Colors".

After that you can use a simple VLOOKUP to get the name of the color you're looking for:

=VLOOKUP(A1,Colors,2,FALSE)

This returns the name of the background color of A1 - but only if your background color can be found from the Colors-range.

You can also write the color values & matching names in your UDF. Here's my try with the same 3 colors:


You need the ColorNumber function to get the matching color values but after you have put them all in the ColorName function you can simply type

=ColorName(A1)

to get the name of the background color in A1.

jim may said:

Peter,

This =Get.Cell() function goes back years ago - MS created it obvioulsy to
satisfy certain requirements that couldn't be satified any other way (I guess).

The function has not been recognized since xl97 or before and there is no
2003, or 2007 equivalent. Can you shed further light on this function?

Thanks for your input!

Jim

Click to expand...

Jim

I don't know much about the history or the theory, I just (very rarely) use it.
This thread has a bit more information. I'm also going to make a separate post here in this thread shortly with some additional comments.

Peter_SSs said:

Edit: This may not update as readily as you might like but I think this could be addressed with some vba code but, unless somebody else steps in, it will have to wait a while because I have to log out for now.

Click to expand...

I think the problem of not updating would be a problem for the code Misca has suggested as well as for my GET.CELL suggestion, since changing the colour of a cell will not trigger a recalculation of the sheet.

Also, I have found on my sheet that one particular cell (B2) does not update very consistently even when I try to force it to by code - see below. I'm wondering if the problem could be related to PGC's comments in post #12 of this thread.

As a result, I have changed the definition of cCol in my sheet from
=GET.CELL(63,!A2)
to
=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

Formula in B2 (copied down) remains the same.

Then I've implemented the following Worksheet_Change code as follows:

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

Whenever you select any cell/range that includes at least one cell in column A and then select any other cell/range, the formulas in column B should re-calculate. I believe this code could be used in conjuction with Misca's functions as well.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> NeedToCalc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> NeedToCalc <SPAN style="color:#00007F">Then</SPAN><br>        Columns("B").Calculate<br>        NeedToCalc = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Columns("A")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        NeedToCalc = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>