Formula to identify colour of another cell

[D_Spark]

I need some help in creating a formula to put the text "GREEN" in cell B2, when cell A2 is coloured GREEN.

If I change the colour of cell A2 to RED I wish the formula in B2 to show "RED" as a word

Anyone able to assist

 

[sanrv1f]

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

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

 

[D_Spark]

Manually

 

[Peter_SSs]

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.

Excel Workbook
	A	B
1	*	*
2	*	Green
3	*	Red
4	*	*
Colour of cell

#VALUE!

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

 

[jim may]

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

 

[Misca]

Try this:

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

Function ColorNumber(MyCell As Range)

ColorNumber = MyCell.Interior.ColorIndex

End Function

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:

Function ColorName(mycell As Range)

Dim X As Long

X = mycell.Interior.ColorIndex

    Select Case X
    
    Case 1
        ColorName = "BLACK"
    Case 3
        ColorName = "RED"
    Case 14
        ColorName = "GREEN"
    Case Else
        ColorName = "COLORNAME UNKNOWN"
    End Select
    
End Function

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.

 

[Peter_SSs]

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

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]

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.

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>