Solved: Count filled cells

[Tewhano]

I’ve programmed VBA for Access but never for Excel and now I am need of some assistance to finish a project I started. I have a spreadsheet for calculating hours worked and the work week schedule for the security staff. I want to be able to fill the cells with a color for the hours the employee is scheduled to work and then calculate the cells fill to return a number of those hours. Is there a function such as if-then that I can use in the total field that ‘counts’ the filled cells and places that number in the total cell?
Not sure if it makes any difference but I am using a one place decimal number to indicate that one cell equals ½ hour. So the function need to sum .5 for every filled cell.

[mdmackillop]

I'm not clear if you wish to Count or Sum to the total cell.
=COUNT(A1:A5) will count cells with number data. COUNTA will count text cells as well.

You can post a sample workbook using Manage Attachments in the Go Advanced reply section if this would assist.

[Paul_Hossler]

I want to be able to fill the cells with a color for the hours the employee is scheduled to work and then calculate the cells fill to return a number of those hours


Like how cells are filled with the color GREEN, etc.?

If so, that's a little harder than a COUNT() formula

Paul

[mdmackillop]

To count number of coloured cells, have a look at XLD's site (http://www.xldynamic.com/source/xld.ColourCounter.html)

[Tewhano]

Yeah Paul_Hossler, that is what I am trying to say. Counting or summing the number of cells that are filled with the color red.

Thanks mdmackillop, that is what I am looking for.

[Tewhano]

Well that was a short lived victory. I thought it was too simple to be true.:doh: I added the code below to the cell I want the total in:

=SUMPRODUCT(--(ColorIndex(B5:AG5)=3))

All I get is the error message:

The formula contains unrecognized text

Not sure what part of the formula it doesn’t like. Any ideas? By the way I forgot to mention that I am using Excel 2010 if that makes any difference.

I noticed that there was a section devoted to SUMPRODUCT, should I be asking my questions over there?

[mdmackillop]

Hi Tewhano,
Scroll down that page and copy the whole of the Code Samples code into a Standard module in your workbook.

[Bob Phillips]

As MD says, it looks as though you have not added the VBA code to your project as well as using the formula. The formula uses the ColorIndex function, which is not native to Excel, but is provided on that page.

The SUMPRODUCT forum is not really the right place, the solution delivers using SP< but it is the VBA that is the engine here.

[Tewhano]

Yep, got the code saved in a module. I saved the workbook as a macro enabled worksheet (.xlsm). I then added the ‘Developer’ tab to the ribbon as well. I went into Macro Security and enabled macros. Tried to recalculate but still no go. I have to say that none of this looks familiar to me. I thought VBA was the same (to some degree) in all the applications in Office.

[Bob Phillips]

Post your workbook and let us take a look.

[Tewhano]

I hope this worked, here is my workbook.

[mdmackillop]

Your workbook works for me, and using, but divided by 2 for the half hour blocks. Try closing and reopening Excel for the enabled macro setting.

[Tewhano]

I tried that but no joy. You think maybe I have some setting that is preventing the function to run? I've used Excel for years but this is my first try at VBA for Excel. I might not have some property enabled or something??

[mdmackillop]

Here's a simpler solution with no VBA required. It uses conditional formatting to colour the cells red when "x" is entered and used COUNTA to total the cells; the "x" does not show as the cells are Custom Formatted as ";;;".

Re your post. Check Macro Security in the Developer tab. It is probablly set too high.

[Bob Phillips]

WSorks fine for me gtoo, but remember that changing a colour will not triggere a recalculate, so you won't get the value refreshed by changing a cell.

[Tewhano]

Not sure what I did but it now works but not totally. I’m guessing that a combination of changes I made in the Macro Security option and on the worksheet fixed it but I’m not sure which since I made and unmade many changes. What is happening now is when I plug the formula in the cell it works but if I change the color of the cells and click the calculate sheet icon nothing changes. Should I put a button on the worksheet to force the formula to run?

I was unable to get COUNTA to work however?? It just returned a zero.

I want to thank you guys for helping me out. Although this is my first try at coding Excel I feel stupid that I hadn’t been able to figure it out on my own.

[mdmackillop]

COUNTA will work on the attached sample, if you add an "x" to the grid area.

[Tewhano]

Thanks, I missed the part about my putting an x in the cell, I thought you meant it put an x in the cell not me. How does that work by the way?

Edit: I see now, you used conditional formatting, very clever....thanks