Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test cell interior color by worksheet function?
I'm trying to conditionally test a cell interior color, by worksheet function
(not VBA). I intend to use the responses to permit varied numerical consequences... I've seen no Excel Help addressal on this, just on text (contents) coloration. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test cell interior color by worksheet function?
=CELL("color",C6)
This is a volitale formula. It states that it works best with numbers for calculations. I cant seem to get it to work with an if() statement. Maybe you could come up with another qualifier. What are you basing your conditional formatting on. If you were to use this as you qualifier it would probably be easier to use in your calculations. God Bless Frank Pytel http://groups.google.com/group/excel...mming?lnk=iggc "Skimmer" wrote: I'm trying to conditionally test a cell interior color, by worksheet function (not VBA). I intend to use the responses to permit varied numerical consequences... I've seen no Excel Help addressal on this, just on text (contents) coloration. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test cell interior color by worksheet function?
The CELL("color",Ref) function returns either 1 or 0, indicating whether
negative numbers are displayed in color. Overall, it is a useless function. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Frank Pytel" wrote in message ... =CELL("color",C6) This is a volitale formula. It states that it works best with numbers for calculations. I cant seem to get it to work with an if() statement. Maybe you could come up with another qualifier. What are you basing your conditional formatting on. If you were to use this as you qualifier it would probably be easier to use in your calculations. God Bless Frank Pytel http://groups.google.com/group/excel...mming?lnk=iggc "Skimmer" wrote: I'm trying to conditionally test a cell interior color, by worksheet function (not VBA). I intend to use the responses to permit varied numerical consequences... I've seen no Excel Help addressal on this, just on text (contents) coloration. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test cell interior color by worksheet function?
Frank,
My understanding of CELL("color",C6) worksheet function is that is looks to see what the content text coloration is - binary answer. If it's property specifies colored for negative numeric value, then it returns a "1". If no colorization is specified for negative numeric value, then it returns "0"... This doesn't address cell interior color(shading /patterns). Perhaps elaboration by example... trying to accomplish: If the cell solid interior color is a pale green, then I want to treat it as "Type A" and look to see what the contents are. The contents will either be alphabetic or numeric, using an "IS" worksheet function will distinguish which. Alphabetic content defaults to highest value assigned a "Type A" event. If the content is numeric, then it will be used to calculate a fraction of the default highest value. Key: HHH = function I'm looking for (for cell color, and using your cell reference "C6") PPG = palette pale-green code Full_Value = 100 So, my cell-checking formula would look something like this: =if(HHH(C6) = PPG, if(ISNUMBER(C6), C6/Full_Value , Full_Value), 0) I'm trying to avoid using macros, as some recipients of the workbook will not be able to use them. R, Skimmer "Frank Pytel" wrote: =CELL("color",C6) This is a volitale formula. It states that it works best with numbers for calculations. I cant seem to get it to work with an if() statement. Maybe you could come up with another qualifier. What are you basing your conditional formatting on. If you were to use this as you qualifier it would probably be easier to use in your calculations. God Bless Frank Pytel http://groups.google.com/group/excel...mming?lnk=iggc "Skimmer" wrote: I'm trying to conditionally test a cell interior color, by worksheet function (not VBA). I intend to use the responses to permit varied numerical consequences... I've seen no Excel Help addressal on this, just on text (contents) coloration. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test cell interior color by worksheet function?
Skimmer;
Probably Chip is highly accurate. Like I said, What is the conditional formatting that turns this cell to the shade that you want it. You should concentrate your formula on this value and use it as a qualifier. For instance, you set your conditional formatting in C6 to read if=to1, cell shade = ppg Then your qualifier can be the number 1. I think I see another question, that is you are using the hex number of the color for some calculation. Create an array of hex numbers and reference this with a lookup() to determine what your next value will be. You can set your qualifiers to equal whatever hex number you wish to assign them in column a with the hex value in column b. God Bless Frank Pytel http://groups.google.com/group/excel...mming?lnk=iggc "Skimmer" wrote: Frank, My understanding of CELL("color",C6) worksheet function is that is looks to see what the content text coloration is - binary answer. If it's property specifies colored for negative numeric value, then it returns a "1". If no colorization is specified for negative numeric value, then it returns "0"... This doesn't address cell interior color(shading /patterns). Perhaps elaboration by example... trying to accomplish: If the cell solid interior color is a pale green, then I want to treat it as "Type A" and look to see what the contents are. The contents will either be alphabetic or numeric, using an "IS" worksheet function will distinguish which. Alphabetic content defaults to highest value assigned a "Type A" event. If the content is numeric, then it will be used to calculate a fraction of the default highest value. Key: HHH = function I'm looking for (for cell color, and using your cell reference "C6") PPG = palette pale-green code Full_Value = 100 So, my cell-checking formula would look something like this: =if(HHH(C6) = PPG, if(ISNUMBER(C6), C6/Full_Value , Full_Value), 0) I'm trying to avoid using macros, as some recipients of the workbook will not be able to use them. R, Skimmer "Frank Pytel" wrote: =CELL("color",C6) This is a volitale formula. It states that it works best with numbers for calculations. I cant seem to get it to work with an if() statement. Maybe you could come up with another qualifier. What are you basing your conditional formatting on. If you were to use this as you qualifier it would probably be easier to use in your calculations. God Bless Frank Pytel http://groups.google.com/group/excel...mming?lnk=iggc "Skimmer" wrote: I'm trying to conditionally test a cell interior color, by worksheet function (not VBA). I intend to use the responses to permit varied numerical consequences... I've seen no Excel Help addressal on this, just on text (contents) coloration. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test cell interior color by worksheet function?
Chris,
Am I being clear as mud, or do you understand what I'm asking? My task: to examine cells into which a User has placed two pieces of information - (1) an alphabetic or numerical content and (2) a cellular interior color fill. I examine the cellular interior color fill, and based upon this I check for content - giving full credit for alphabetic data, or a decimal calculation for numeric content. The expression I expect to take the form: =if(HHH(C6) = PPG, if(ISNUMBER(C6), C6/Full_Value , Full_Value), 0) Here, I use: (a) "HHH" as a placeholder name for the Worksheet Function I'm requesting help to identify, which can extract the cellular interior color fill information from the cell. (b) "PPG" as a variable I declare, which holds the numerical code for the palette color of interest. Will I be forced to go to VBA in order to implement this screening? Or, is there a worksheet function which returns the necessary fill code? I haven't seen one, so far. And, you matched my understanding of the CELL function. R, Tom "Frank Pytel" wrote: Skimmer; Probably Chip is highly accurate. Like I said, What is the conditional formatting that turns this cell to the shade that you want it. You should concentrate your formula on this value and use it as a qualifier. For instance, you set your conditional formatting in C6 to read if=to1, cell shade = ppg Then your qualifier can be the number 1. I think I see another question, that is you are using the hex number of the color for some calculation. Create an array of hex numbers and reference this with a lookup() to determine what your next value will be. You can set your qualifiers to equal whatever hex number you wish to assign them in column a with the hex value in column b. God Bless Frank Pytel http://groups.google.com/group/excel...mming?lnk=iggc "Chip Pearson" wrote: The CELL("color",Ref) function returns either 1 or 0, indicating whether negative numbers are displayed in color. Overall, it is a useless function. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Skimmer" wrote: Frank, My understanding of CELL("color",C6) worksheet function is that is looks to see what the content text coloration is - binary answer. If it's property specifies colored for negative numeric value, then it returns a "1". If no colorization is specified for negative numeric value, then it returns "0"... This doesn't address cell interior color(shading /patterns). Perhaps elaboration by example... trying to accomplish: If the cell solid interior color is a pale green, then I want to treat it as "Type A" and look to see what the contents are. The contents will either be alphabetic or numeric, using an "IS" worksheet function will distinguish which. Alphabetic content defaults to highest value assigned a "Type A" event. If the content is numeric, then it will be used to calculate a fraction of the default highest value. Key: HHH = function I'm looking for (for cell color, and using your cell reference "C6") PPG = palette pale-green code Full_Value = 100 So, my cell-checking formula would look something like this: =if(HHH(C6) = PPG, if(ISNUMBER(C6), C6/Full_Value , Full_Value), 0) I'm trying to avoid using macros, as some recipients of the workbook will not be able to use them. R, Skimmer |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test cell interior color by worksheet function?
Skimmer;
Let me be blunt. Chip an I both agree. The cell("color","") function is a crap function for what you are attempting to do. No you can not identify the color of the cell using a function to perform a calculation. You have some options. VBA?(I don't know. I would suggest you post your question in Excel.programming). There is a work around function and it might go something like this. Try to determine if there is a "Conditional Format" placed on the cell. You can identify this in 2003 by naving to FormatConditional Formatting. If there is a conditional format on this cell that says. If this cell is "something" then color the cell PPG. If this is the case you can use the "something" as a qualifier to create your calculation. For instance, lets make the assumption that the color of the cell changes when the number 1 (The number 1 is the "something" that I refered to earlier) is entered. If this is the case then your equation would read as follows: =if(a1=1, if(ISNUMBER(C6), C6/Full_Value , Full_Value), 0) That is one of the ways that you might complete this equation. You are not going to calculate anything using the cell("color","") function. God Bless Frank Pytel http://groups.google.com/group/excel...mming?lnk=iggc "Skimmer" wrote: Chris, Am I being clear as mud, or do you understand what I'm asking? My task: to examine cells into which a User has placed two pieces of information - (1) an alphabetic or numerical content and (2) a cellular interior color fill. I examine the cellular interior color fill, and based upon this I check for content - giving full credit for alphabetic data, or a decimal calculation for numeric content. The expression I expect to take the form: =if(HHH(C6) = PPG, if(ISNUMBER(C6), C6/Full_Value , Full_Value), 0) Here, I use: (a) "HHH" as a placeholder name for the Worksheet Function I'm requesting help to identify, which can extract the cellular interior color fill information from the cell. (b) "PPG" as a variable I declare, which holds the numerical code for the palette color of interest. Will I be forced to go to VBA in order to implement this screening? Or, is there a worksheet function which returns the necessary fill code? I haven't seen one, so far. And, you matched my understanding of the CELL function. R, Tom "Frank Pytel" wrote: Skimmer; Probably Chip is highly accurate. Like I said, What is the conditional formatting that turns this cell to the shade that you want it. You should concentrate your formula on this value and use it as a qualifier. For instance, you set your conditional formatting in C6 to read if=to1, cell shade = ppg Then your qualifier can be the number 1. I think I see another question, that is you are using the hex number of the color for some calculation. Create an array of hex numbers and reference this with a lookup() to determine what your next value will be. You can set your qualifiers to equal whatever hex number you wish to assign them in column a with the hex value in column b. God Bless Frank Pytel http://groups.google.com/group/excel...mming?lnk=iggc "Chip Pearson" wrote: The CELL("color",Ref) function returns either 1 or 0, indicating whether negative numbers are displayed in color. Overall, it is a useless function. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Skimmer" wrote: Frank, My understanding of CELL("color",C6) worksheet function is that is looks to see what the content text coloration is - binary answer. If it's property specifies colored for negative numeric value, then it returns a "1". If no colorization is specified for negative numeric value, then it returns "0"... This doesn't address cell interior color(shading /patterns). Perhaps elaboration by example... trying to accomplish: If the cell solid interior color is a pale green, then I want to treat it as "Type A" and look to see what the contents are. The contents will either be alphabetic or numeric, using an "IS" worksheet function will distinguish which. Alphabetic content defaults to highest value assigned a "Type A" event. If the content is numeric, then it will be used to calculate a fraction of the default highest value. Key: HHH = function I'm looking for (for cell color, and using your cell reference "C6") PPG = palette pale-green code Full_Value = 100 So, my cell-checking formula would look something like this: =if(HHH(C6) = PPG, if(ISNUMBER(C6), C6/Full_Value , Full_Value), 0) I'm trying to avoid using macros, as some recipients of the workbook will not be able to use them. R, Skimmer |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test cell interior color by worksheet function?
It's clear to me that this line of discussion has a fundamental problem...
Understanding is the key to solution, and we still aren't connecting. My understanding is that EXCEL has two modes of operation with functions: (1) Worksheet (types include - database, date and time, engineering, financial, information, logical, lookup and reference, math and trigonometry, statistical, text, and external) (2) VBA macro (more than I can list here, some of which resemble worksheet functions with different spellings) Sometimes we can invoke worksheet functions in VBA macro through a specific invocation (e.g. Application.WorksheetFunction.Max). I'm trying not to resort to macros, because the workbook will be used by folks who won't be able to use them. You brought up CELL as a worksheet Information Function - I didn't. The documentation on CELL options didn't appear to address "interior color fill". Macros can refer to the background cell color /pattern by invoking commands like: Workbooks("Book1").Worksheets("Sheet1").Range("Ran ge1").Interior.ColorIndex or, With Worksheets("Sheet1").Rectangles(1).Interior .Pattern = xlChecker .PatternColorIndex = 5 End With or, With Worksheets("Sheet1").Rectangles(1).Interior .Pattern = xlGrid .PatternColor = RGB(255,0,0) End With or, Worksheets("Sheet1").Range("A1").Style.IncludePatt erns <boolean so that the color's /pattern's code [unique identfier(s)] can be used as an argument for IF...THEN filtering. I'm looking for a non-macro, worksheet function that can address the interior fill color /pattern. The colors that appear in my spreadsheet cells of interest, are independent of the alphabetic /numeric contents values that accompany them. Apparently, there is no such defined MS Excel worksheet function that extracts that information. That being the case, the response to my question should be "No, there is no such simple solution without going macro." That would have satisfied the query. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell interior color | Excel Discussion (Misc queries) | |||
Need Excel Formula/Function to color cell interior | Excel Worksheet Functions | |||
Logical Test comparison using cell color | Excel Discussion (Misc queries) | |||
color the interior of a range | Excel Worksheet Functions | |||
Print without Interior Color | Excel Discussion (Misc queries) |