State Another Cell's Fill Color as Text

[DOUG]

How would I construct a text statement to point to another cell, which
happens to be green, and have it say (GREEN). This will become part of a
larger string of text which re-states in English what the spreadsheet is
saying in numbers. (Some people relate to spreadsheets amd some want it told
to them as a little story).

DOUG ECKERT

 

[Shane Devenshire]

Hi,

To the best of my knowledge this is going to be quite a challenge.

You could do this with a macro in 2003 since there are only 64 or so colors,
but in 2007 there are 16 million possible colors. And the macro - custom
function maybe - will require one condition per color.

 

[Jacob Skaria]

Hi Doug

Try the below UDF. You have got just 8 colors here..the basic ones..You can
add more as required...Try this in any cell. The formula would be
=getcolortext(C11)

If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the below code. Save. Get back to
Workbook.

Function GetColorText(varRange As Range)
Dim arrTemp As Variant
Dim varTemp As Variant
varTemp = varRange.Interior.ColorIndex
arrTemp = Array("No Fill", "Black", "White", "Red", _
"Green", "Blue", "Yellow", "Magenta", "Cyan")
If varTemp = -4142 Then varTemp = 0
If varTemp >= 0 And varTemp <= 8 Then
GetColorText = arrTemp(varTemp)
End If
End Function

If this post helps click Yes

 

[DOUG]

Jakob: That resulted in an error "#NAME?". Was I supposed to place a cell
reference of some kind within the VBA instructions?

DOUG

 

[DOUG]

Jakob: Actually, I am trying to get the color of the fill in the referenced
cell, rather than the color of the text...

DOUG

 

[DOUG]

Jakob: 'Any additional suggestions on this? I did try pasting the statement
into the VBA instructions, with no luck. Was there something else I needed
to do to make this work?

By the way, your suggestions have been very helpful. I now have the updates
pasted into text as complete sentences. This will make future updates to
management much easier.

DOUG

 

[Jacob Skaria]

Doug

I found your response to the "text + %" query asking about the
getcolortext...Here we go..

1. Have you pasted the code to VBE which is launched using Alt+F11 from
workbook. From menu 'Insert' a module and paste the code

2. Fill color in cell A1 (say Yellow)

3. In B1 enter the formula
=getcolortext(A1)

4. This should result "Yellow' in B1.


If this post helps click Yes

 

[DOUG]

Jakob: I believe I tried that. Today, just now, I was going to copy the VBA
code from the spreadsheet and send it to you. However, when I selected ALT +
F11, I was directed to a blank screen. I do not know why that happened...

DOUG