How do I count shaded cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have built a scheduling templet and would like to be able to count the
number of cells that shaded. Where shaded cells represent hours worked. Any
ideas?
 
Thanks,

i looked at that but had trouble when I put the counting formula into my
cell. It returns a #NAME? error.

My cells are in row a7:z7

so i modifed the forumal to read

=SUMPRODUCT(--(ColorIndex(C7:Z7)=3))

I am not sure that the -- mean before the second (. I do not remeber seeing
these before.

Any suggestions?
 
Thanks bob,

i tried that but get a #NAME? error.

I took the forumal at the site you sent and set up for my sheet as below

=SUMPRODUCT(--(ColorIndex(C7:Z7)=3))

Any suggestion on what I may have done wrong?

Thanks
Randy
 
You have to install the UDF first, copy the code and paste into a module in
the workbook or make an add-in by pasting into module in a new wrokbook and
save as ColorIndex.xla, then restart excel and check it under tools>add-ins

Regards,

Peo Sjoblom
 
Did you put the code in a normal code module, not a sheet module?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks Peo,

I have to admit i have never written any code in xcell outside formulas.
Any suggestion on where I could find info to what you suggested?

Randy
 
You don't have to write it, it is all on the web page. Just copy and paste
it.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I followed these directions on the MS web page

On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu in the Microsoft Visual Basic window, click Module.
In the Modulen window, type the code for your function.

<here I copied in the text from the referenced page>

On the File menu, click Close and Return to Microsoft Excel.
On the worksheet, use your function in formulas as you would any worksheet
function.

I no longer get a #NAME? error but it still does not return a value. Do you
know how you know for sure whta the value of a color is on the pallet. Since
they are laid out in gride I assumed counting starts top left then progresses
like reading. I may be that I have the wrong value for the color.

Thanks again
 
Randy

To find out the cell color before using it in your formula.........

In an empty cell enter =ColorIndex(cellref) where cellref is the colored
cell.

This will reurn a number to use in the other formula.

NO, the color grid is not laid out as you suggest.

To get the index numbers and colors run this macro which adds a worksheet with
the colors and index numbers.

Copy/paste to the Module you used for Bob's ColorIndex code.

Sub ListColorIndexes()
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx))
Cells(Ndx, 3).Value = Ndx
Next Ndx
End Sub

You'll be so VBA'ed by the time you finish this project, you'll be writing
your own.


Gord Dibben Excel MVP
 
thank you very much

I have it working. It is really cool. the user only has to block the cells
for the hours they want each person to work in a GANT chart style. Then
recalculate the sheet and it does all the hours.

I can get around needing to manually recalcuulate evidently. The MS site
notes that the program must be manually recalcualted using Ctrl-Alt-F9. I
tries puting this into a macro but it just locks the system up.

Anway its great now and eventually I solve the other as well.

thanks to all who helped me today.
 
Randy,

Glad Gord sorted you out. A couple of comments.

Randy said:
thank you very much

I have it working. It is really cool. the user only has to block the cells
for the hours they want each person to work in a GANT chart style. Then
recalculate the sheet and it does all the hours.
Click to expand...

Not bad is it :-)
I can get around needing to manually recalcuulate evidently. The MS site
notes that the program must be manually recalcualted using Ctrl-Alt-F9. I
tries puting this into a macro but it just locks the system up.
Click to expand...

I don't think you will, it is a limitation. What I do is create a toolbar
with the relevant colours on it, then rather than have a user put the colour
in themseloves, they select the cells, then click the colour on the toolbar.
That way, it calls my code which I can force a recalculation.
 
Peo
I have the sheet working but would like to be able to coly the color of a
cell on one work sheet automatically to another. Any suggestion on how I can
id the color of a cell on another page than set the color of that cell to the
same?
 
I have the sheet working but now find i need to automatically copy the
shading a cell on one worksheet to a cell on an other. I have looked through
the various help sections and around the board but have not found anything.
Any suggestions?
 
I have the sheet working but now find i need to automatically copy the
shading a cell on one worksheet to a cell on an other. I have looked through
the various help sections and around the board but have not found anything.
Any suggestions?
 
I have the sheet working but now find i need to automatically copy the
shading a cell on one worksheet to a cell on an other. I have looked through
the various help sections and around the board but have not found anything.
Any suggestions?
 
Edit/Copy the source cell. Select the destination cell and Edit/Paste Special
and select Formats.
 
Thanks.

Your are right this works. however, i am looking for an automated process
as there are many cells involved and the users are not very computer literate.

based on these comments are ther any other apporaches you might have seen?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Format Types 3
SUMIF based on cell shading 1
macro to disallow shading in certain columns 2
How can I count the number of cells with a particular color/shade 2
Cell Shading 2
Calculating percentage of Shaded Cells in a row 1
how do I carry cell colors? 2
sort 2

Back
Top