Color in Excel 1

[Scott24x7]

I wanted to do some counting based on the background color of a cell, but I can't find a command that supports that. Is there some way to determine the fill color or font color of a cell within a cell? I'd prefer to not resort to VBA for this.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Andrzejek]

You can try:
[pre]

Filter
- Filter by Color
- Filter by Cell Color, or
- Filter by Font Color
[/pre]

But I don't know how you can Count these.

You may also investigate Conditional Formatting

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

HI,

Color is such a poor statistical data element as it tends to be inexact: shades of...

And with color, you have THREE combined RGB values as you probably know.

I'll bet that someone in your company thought it was a great idea to "tag" certain data with colors for meaning. It would have been much MUCH better to have used Conditional Formatting to color emphasize BASED ON DATA VALUES. Then we wouldn't be having this conversation.

But I've had to rescue some hapless users from time to time by constructing a very simple user defined function to return the color index...

Function WhatColorIndex(rng As Range)
   WhatColorIndex = rng.Cells(1,1).Interior.ColorIndex
End Function

Code in a module and use as any other spreadsheet function in a cell.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

As far as Andy's suggestion, simply count on ANY column using the SUBTOTAL() function. Note that there are TWO sets of categories. One set only aggregates VISIBLE cells. THAT'S the one to use.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Scott24x7]

Thanks guys. The problem is, I'm looking at a pretty large range of data (From B5:CG54) which is something like 2850 cells. Data is background color coded by "group" so they can be anywhere. The problem then is some cells share group, but have a different group code. (So DR and DRA are the same, but they are the only two out of about 15 colors.) Was hoping to make it dynamic but I also need to match that color code with the "party" that won in a column (yeah, this is voter data...) Maybe a better way to fix that is create a hidden row, and put the code of the party that one in any given year there, and then use that to compare instead of color... that might just work, and I can shove it way down the page so even hidden it won't look "weird" when there is a row number missing.

I think that's going to be the way to go! I really need to avoid any MACRO code, as we can't turn that feature on for "security" purposes...
Cheers and many thanks.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

So is the coding restriction simply to keep THAT workbook "clean" or could you put your code in another workbook that could the analyze THAT workbook/sheet?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Scott24x7]

No, the file has to remain "free-standing", and there can be no VBA macro code, linked or otherwise.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Can you upload a representative manageable example (maybe 3 rows and 5 columns) along with the expected results? This intrigues me.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

... or maybe the entire Excel file - as long as it is NOT a top secret information.
We don't want another Watergate scandal here.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Yes I was referring to uploading a sample workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}