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
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
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
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
with count white color
Answer-Color-Query.xlsm
Answer-Color-Query.xlsm
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
Yes, adding the functions to a new module is correct. Then use the counting functions anywhere in your spreadsheet.
Dave
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 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
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
Glad you liked it!
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thank you,
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
I was not sure if I could just add to this questionn or post another one so I posted another.
Thanks again,
Blueyes727
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
The color is the interior.color (so for vba you can use vbRed, vbGreen, etc.)
Dave
Be sure to ask a related question.
Dave
ASKER
That's great, thank you very much for you help.
Blueyes727
Blueyes727
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
This should be most efficient!
See attached,
Dave
SAMPLE-BLUEYES727-r3.xlsm
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
Blueyes727
show me the data
ASKER
I've added the file, it's the last block on the bottom.
Thank you,
Blueyes727 SAMPLE-BLUEYES727-r3.xlsm
Thank you,
Blueyes727 SAMPLE-BLUEYES727-r3.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Answer-Red-Color.xlsm