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?

"Randy" wrote:

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?


"Gord Dibben" wrote:

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

On Mon, 31 Jan 2005 14:13:01 -0800, "Randy"
wrote:

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

"Bob Phillips" wrote:

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" wrote in message
...
Sorry I gave you the wrong reference i used this link to find a formula

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

"Randy" wrote:

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
"Bob Phillips" wrote:

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" wrote in message
...
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?