I'm looking for a way to count the number of cells in a set range in a given row that have a fill color. I have a spreadsheet that has a range of columns that corresponds to a half hour for each column ranging from 6:00 am to 8:00 pm. Each row corresponds to a different day of the month (e.g., 1-31). I would like the user to shade the cells for a particular day indicating the hours they worked. I then would like to put a formula in a different column that would calculate the number of hours worked based on the cells that were shaded in a particular color (e.g., black). Another column would calculate travel time by counting number of cells shaded in red. (See below)
Formulas: Counting cells with fill color
-
-
-
A couple of handy links (hopefully!)
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
http://www.ozgrid.com/VBA/Sum.htm -
There is some code & a custom colour count function written by Dave in his Tips & Tricks download that retails for around £4
See my signature....
Not that I'm just "pushing" Dave's stuff, but I just think it would be unfair of me to just reproduce it for nothing
BTW.... that download includes about 30 other cool tricks...
Will
-
Hi Starman,
Keep in mind when you are doing this that a change of colour will not trigger an event in Excel, such as a Calculate event. Therefore, if you change a colour after you have entered the UDF it will not automatically update its value - it will be up to you to remember to recalculate.
An alternative might be to get the users to enter something in the cells, say a 1, and have this cause the cell to be coloured using Conditional Formatting. You can then use the 1s to do your counting (you could always set the font colour to the same as the chosen background colour if you don't want the 1s to show).
Just my 2p worth ;;)
-
Thank you Iridium. Right on the money!
Thanks also Richie & Will. I have a macro that I use to create reports from the associated worksheets. I inserted a "Calculate" into that macro before I create the reports.
-
-
Re: Formulas: Counting cells with fill color
Try it,
Color cells in column A
select B1 to define a name like X=get.cell(38,a1)+now()*0
Col B is a "helper" column to put the Name's results
then you can count the numbers of the different colors in the cells
Take a look at the attachment,it will show you how to work with this means.
regards,
Apollo Huang
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!