Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Randy
 
Posts: n/a
Default How do I count shaded cells

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?
  #2   Report Post  
Paul B
 
Posts: n/a
Default

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

"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?



  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

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?



  #4   Report Post  
Randy
 
Posts: n/a
Default

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?


"Paul B" wrote:

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

"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?




  #5   Report Post  
Randy
 
Posts: n/a
Default

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?






  #6   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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 toolsadd-ins

Regards,

Peo Sjoblom

"Randy" wrote:

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?


"Paul B" wrote:

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

"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?




  #7   Report Post  
Randy
 
Posts: n/a
Default

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?




  #8   Report Post  
Bob Phillips
 
Posts: n/a
Default

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?





  #9   Report Post  
Randy
 
Posts: n/a
Default

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

"Peo Sjoblom" wrote:

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 toolsadd-ins

Regards,

Peo Sjoblom

"Randy" wrote:

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?


"Paul B" wrote:

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

"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?



  #10   Report Post  
Bob Phillips
 
Posts: n/a
Default

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

"Peo Sjoblom" wrote:

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

toolsadd-ins

Regards,

Peo Sjoblom

"Randy" wrote:

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?


"Paul B" wrote:

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

"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?







  #11   Report Post  
Randy
 
Posts: n/a
Default

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?






  #12   Report Post  
Gord Dibben
 
Posts: n/a
Default

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?







  #13   Report Post  
Randy
 
Posts: n/a
Default

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.

"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?








  #14   Report Post  
Bob Phillips
 
Posts: n/a
Default

Randy,

Glad Gord sorted you out. A couple of comments.

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


  #15   Report Post  
Randy
 
Posts: n/a
Default

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?

"Peo Sjoblom" wrote:

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 toolsadd-ins

Regards,

Peo Sjoblom

"Randy" wrote:

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?


"Paul B" wrote:

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

"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?





  #16   Report Post  
Randy
 
Posts: n/a
Default

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?


"Paul B" wrote:

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

"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?




  #17   Report Post  
Randy
 
Posts: n/a
Default

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?








  #18   Report Post  
Randy
 
Posts: n/a
Default

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?

"Bob Phillips" wrote:

Randy,

Glad Gord sorted you out. A couple of comments.

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



  #19   Report Post  
Myrna Larson
 
Posts: n/a
Default

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

On Thu, 3 Feb 2005 09:41:43 -0800, "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?

"Bob Phillips" wrote:

Randy,

Glad Gord sorted you out. A couple of comments.

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




  #20   Report Post  
Randy
 
Posts: n/a
Default

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?








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shaded cells won't print shaded Linda C Excel Worksheet Functions 4 April 3rd 23 06:59 PM
Selecting shaded cells in a row Al Excel Worksheet Functions 5 January 25th 05 06:31 AM
Count non-colored cells Ken G Excel Discussion (Misc queries) 3 January 2nd 05 01:42 PM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM
Count cells with data shoiley New Users to Excel 5 November 28th 04 08:23 PM


All times are GMT +1. The time now is 11:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"