Avatar of blueyes727
blueyes727
 asked on

Counting cell color in columns of different row sizes

Hello all,

I am working with Excel 2010.

I am trying to find a way to count the number of columns in specific rows which are either white or yellow.  The columns can be anywhere from 3 rows high to about 20 rows high.

The row lengths will change all the time and the column heights will be changing also.

The data in the cells is not important at this point, only the cell colors matter.  I will need the data after I have counted those cells.

To make it easier I figure I could sort out all the rows which are 3, 4, 5 high etc. and just work with one size at a time.

I have attached a sample excel spreadsheet.

I have searched the site and can not find anything that will do this.  .Mind you I do not know enough about macro's to know if I have come across something that would anyway.

I hope someone can help me with this.  I am familiar with Excel but not with Macro's.

I awarded 500 points for this question as I think this is going to be quite challenging.  If it is not, please accept my apologies.  As I said, I know nothing about Macro's.

Thank you,

Blueyes727 SAMPLE-BLUEYES727.xlsx
Microsoft ApplicationsMicrosoft Excel

Avatar of undefined
Last Comment
blueyes727

8/22/2022 - Mon
ukerandi

click on run button
Answer-Red-Color.xlsm
ukerandi

complete code
click on Run button
Answer-Red-Color.xlsm
blueyes727

ASKER
Ukerand,

Thank's for the file.

I tried it but it only has one button on the sheet and gives me a total of colored cells.

If you look at tthe sample I posted, I need a total at the end of each row with how many columns are white (blank) and how many columns are yellow.  That's just a sample, I have a lot of columns which are 3, 4 and 5 or more rows high which I must count.  So just a total of the colored and white cells will not do.  I need to know how many columns are yellow and white.

Blueyes727
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ukerandi

with count white color
Answer-Color-Query.xlsm
blueyes727

ASKER
Ukerand,

You do not understand what I need.

If you look at the sample I've attached, in column "A" it says:
THIS COLUMN NOT COUNTED
So I do not need to count column "A" or "red" cells.

Secondly, the first 5 rows are one item.  I need to count how many columns in those 5 rows are either all white or all yellow.  At the end I need it to note how many columns are "white" and how many are "yellow" as shown in the sample I attached.

Then there is the second item which is 3 rows only.  The same applies here.  I need to count how many columns in those rows are either all white or all yellow.  At the end I need it to note how many columns are "white" and how many are "yellow" as shown in the sample I attached.

Just a quick count of how many colored cells I have is not what I am looking for.

Thank you,

Blueyes727
SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
blueyes727

ASKER
dlmille

I added the code you gave me to Excel 2010 but when I click on "Macro" to select the Macro to run nothings is there to select.

Any ideas.

I hit ALT+F11, then "Insert Module", pasted the code, did ALT "Q" to save.  This is how I usually do it and then click on "Macro" and there is usually a title I can select to run the Macro but in this case there is nothing.

Thank you,

Blueyes727
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
blueyes727

ASKER
Dave,

I also downloaded the file you attached it seems to work very well but again, I can't select the Macro to run it.  How do I run this Macro?

Thank you,

Blueyes727
dlmille

THere is no macro.  These are functions.  Look at the spreadsheet and see where the #'s are - that's where the functions are.

Yes, adding the functions to a new module is correct.  Then use the counting functions anywhere in your spreadsheet.

Dave
blueyes727

ASKER
Hi Dave,

I managed to get this thing running and it's perfect.


Thank you very miuch.  I can copy and past to other columns of the same size and it works great.

Thanks again,

Blueyes727
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dlmille

whew - I thought you needed a macro to place the functions where they needed to be as well.  I was working that, but I'll stop now.

Glad you liked it!

Dave
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
blueyes727

ASKER
I'm not sure how to use the 3rd window in the Macro (MyColorLong).  This would be very handy to have.

Thank you,

Blueyes727
blueyes727

ASKER
By the way, I just posted another question.  The last thing I'll have to do with this is to sort the columns using the "Yellow" totals.

I was not sure if I could just add to this questionn or post another one so I posted another.

Thanks again,


Blueyes727
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

If you put a color number in that parameter, it uses that number for comparison, as opposed to a range.

The color is the interior.color (so for vba you can use vbRed, vbGreen, etc.)

Dave

Be sure to ask a related question.

Dave
blueyes727

ASKER
That's great, thank you very much for you help.

Blueyes727
dlmille

Ouch.  Driving home today, I realized I should have been using UsedRange to determine the last column to search (and the last row, for that matter).

This should be most efficient!

See attached,

Dave
SAMPLE-BLUEYES727-r3.xlsm
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
blueyes727

ASKER
I must be missing something because after I have selected the area and color I get huge number like 45 when I only have 2 or 3 columns which are the same color.

Blueyes727
dlmille

show me the data
blueyes727

ASKER
I've added the file, it's the last block on the bottom.

Thank you,

Blueyes727 SAMPLE-BLUEYES727-r3.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
blueyes727

ASKER
Got it, thank you for you help and patience.

Blueyes727
blueyes727

ASKER
I am very happy with the resolution.

I do not use the site very often but whenever I do I know I will get the answer I am looking for quickly and accurately.  

Thank you,

Blueyes727