Formula to detect color?
 

Is there anyway to detect cell color in a formula? I found the CELL
("color", ref) command, but that doesn't seem to work.

I'm interested in counting shaded cells in a specified range without
using a macro if possible.

Any ideas?

Thanks!

Formula to detect color?
 

Unfortunately XL doesn't have a way of doing what you want to do without
using macros.

Dave
--
Brevity is the soul of wit.


" wrote:

Is there anyway to detect cell color in a formula? I found the CELL
("color", ref) command, but that doesn't seem to work.

I'm interested in counting shaded cells in a specified range without
using a macro if possible.

Any ideas?

Thanks!

Formula to detect color?
 

See Chip's page www.cpearson.com if you want to explore macros to do this
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
Is there anyway to detect cell color in a formula? I found the CELL
("color", ref) command, but that doesn't seem to work.

I'm interested in counting shaded cells in a specified range without
using a macro if possible.

Any ideas?

Thanks!

Formula to detect color?
 

Assume you have a 3x4 array of colors, with color 37 located at B2
The numbers refer to the background (pattern) colors, not some
calculation:

37 44 6 4
38 40 37 35
46 37 12 54

Select B6 and
Insert Name Define Names in workbook: ColorTag
Refers to: =GET.CELL(63,Sheet1!B2)
In B6 enter this formula and fill to E8:
=ColorTag
The result should be an array of numbers as above.
To count the number of cells with the color 37 (pale blue) use
=COUNTIF(B6:E8,37)
After making color changes, refresh with Ctrl+Alt+F9
Always select B6 to edit ColorTag.

Formula to detect color?
 

The GET.CELL function doesn't seem valid. Are you describing a formula
or VBA?

What is the '63' referring to?

Thanks.

Formula to detect color?
 

This is a function from an earlier version of Excel,
but it still works in Excel 2003
For syntax see:

http://www.microsoft.com/downloads/details.aspx?
FamilyID=C09BF7F7-D30E-4CE9-8930-5D03748CA5CD&displaylang=en
or
http://tinyurl.com/ydght4

Formula to detect color?
 

So you're actually using the XL4 macro language to grab the color?
Very creative.

It's been a few years since I programmed in the old macro style. It
looks like you need to download an extra program so XL can process the
old macro syntax?

Are they any references for the old macro language?

Thanks!


wrote:
Is there anyway to detect cell color in a formula? I found the CELL
("color", ref) command, but that doesn't seem to work.

I'm interested in counting shaded cells in a specified range without
using a macro if possible.

Any ideas?

Thanks!

Formula to detect color?
 

When you download XLMacro.exe and run it,
you will create a file called XLMacro.chm at
Program Files/Microsoft Office/Office/1033
This is just a Help file to explain the functions.
No programming is required nor does Excel 2003
need any extra programs.

Formula to detect color?
 

Here is a simple way to count colored cells
without VBA or Excel 4 functions:
Edit Find Format select color or point to sample cell
Select your range
Find All
The Find/Replace window will show the number of
cells colored as specified,
in the lower left corner of the window.

Formula to detect color?
 

The find / replace "Find All" is a neat idea.

My original questions was seeing if there was a formula that would
return the count of colored cells. It looks like using Excel 4 macro
language is the only way to go.

Can you explain what '63' is?

Thanks.