Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
State Another Cell's Fill Color as Text
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
State Another Cell's Fill Color as Text
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "DOUG" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
State Another Cell's Fill Color as Text
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 --------------- Jacob Skaria "DOUG" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
State Another Cell's Fill Color as Text
Jakob: That resulted in an error "#NAME?". Was I supposed to place a cell
reference of some kind within the VBA instructions? DOUG "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "DOUG" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
State Another Cell's Fill Color as Text
Jakob: Actually, I am trying to get the color of the fill in the referenced
cell, rather than the color of the text... DOUG "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "DOUG" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
State Another Cell's Fill Color as Text
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 "DOUG" wrote: Jakob: Actually, I am trying to get the color of the fill in the referenced cell, rather than the color of the text... DOUG "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "DOUG" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
State Another Cell's Fill Color as Text
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 --------------- Jacob Skaria "DOUG" wrote: 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 "DOUG" wrote: Jakob: Actually, I am trying to get the color of the fill in the referenced cell, rather than the color of the text... DOUG "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "DOUG" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
State Another Cell's Fill Color as Text
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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "DOUG" wrote: 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 "DOUG" wrote: Jakob: Actually, I am trying to get the color of the fill in the referenced cell, rather than the color of the text... DOUG "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "DOUG" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I fill one cell color with text html/rgb color from another | Excel Discussion (Misc queries) | |||
is there a keyboard short cut to fill cell's in with color | Excel Discussion (Misc queries) | |||
Finding the vaule of a cell's fill color | Excel Discussion (Misc queries) | |||
Calculating total value based on another cell's state | Excel Discussion (Misc queries) | |||
Change a cell's fill color dynamically? | Excel Discussion (Misc queries) |