How do I count shaded cells

[Randy]

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?

 

[Paul B]

Randy, have a look here

http://www.cpearson.com/excel/colors.htm
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Bob Phillips]

Randy,

There is a counting solution at http://xldynamic.com/xld.ColourCounter.html
which counts coloured cells. If you mean shaded as in pattern, it could be
amended to that as well.

--

HTH

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

 

[Randy]

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?

 

[Randy]

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

 

[Peo Sjoblom]

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

 

[Randy]

Sorry I gave you the wrong reference i used this link to find a formula

http://www.xldynamic.com/source/xld.ColourCounter.html

 

[Bob Phillips]

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)

 

[Randy]

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

 

[Bob Phillips]

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)

 

[Randy]

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

 

[Gord Dibben]

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

 

[Randy]

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.

 

[Bob Phillips]

Randy,

Glad Gord sorted you out. A couple of comments.

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.

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.

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.

 

[Randy]

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?

 

[Randy]

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?

 

[Randy]

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?

 

[Randy]

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?

 

[Myrna Larson]

Edit/Copy the source cell. Select the destination cell and Edit/Paste Special
and select Formats.

 

[Randy]

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?