Formula to detect color?

[robotman]

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!

 

[Guest]

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

Dave

 

[Bernard Liengme]

See Chip's page www.cpearson.com if you want to explore macros to do this

 

[Herbert Seidenberg]

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.

 

[robotman]

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

What is the '63' referring to?

Thanks.

 

[Herbert Seidenberg]

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

 

[robotman]

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!

 

[Herbert Seidenberg]

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.

 

[Herbert Seidenberg]

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.

 

[robotman]

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.