Test cell interior color by worksheet function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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,

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
 
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-applications-and-spreadsheet-programming?lnk=iggc
 
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
 
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 Format>Conditional 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-applications-and-spreadsheet-programming?lnk=iggc
 
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("Range1").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.IncludePatterns <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.
 
There is none. The only way to play, test or otherwise the fil colour of a cell is via the VBA route - there is no non-VBA function throughs whose use you will ascertain the fill colour of a cell. hope this clarifies......Seem dumb the information must be in there somewhere as the cell has changed colour, but MS have not given us the simple funtional tool to extrcat this information (yet).
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.
On Tuesday, September 04, 2007 9:32 AM fpyte 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-applications-and-spreadsheet-programming?lnk=iggc

"Skimmer" wrote:
Click to expand...
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("Range1").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.IncludePatterns <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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top