Searching for applied conditional formatting is extremely difficult in VBA.
If your colors are applied with conditional formatting, then you should be
doing your count based on the underlying condition and not the color of the
cells. then you would probably be better to use formulas instead of VBA as
I originally suggested.

--
Regards,
Tom Ogilvy


"Jane" wrote in message
...
I get what you are saying. I didn't think it through. I can do
conditional
formatting on the CountSummary sheet but can you still help me with the
vba
of counting from the other sheets and that whole procedure? Thanks.

"Mike Fogleman" wrote:

Jane, perhaps your lack of Excel knowledge has caused you to seek a
method
to solve the problem in an obscure way. Excel is a powerful number
cruncher,
not a coloring book. The color is there for highlighting, emphasizing
and
visually appealing, not for basing your calculations. If you would
explain
what you have, where you have it, and where you want to go with it, in
black
& white terms, then we may be able to show you the most efficient way to
get
you there. Once you're there, you can color it 'till your heart's
content.
I don't mean to sound harsh, but I believe you came here to learn
how
Excel can work for you, and I think you took a wrong turn in how to best
use
it. So now, Jane, gather up your problem and explain it to us as if we
were
blind.

Mike F
"Jane" wrote in message
...
Need help with complicated use of count background color of Cell

I have 3 spreadsheets within a workbook:

CountSummary is sheet1
Men is sheet2
Women is sheet3

CountSummary counts the data based on the color of the background
colors
in
the Men and Women spreadsheets

Men consists of data for John and Rob. The information related to
each
are
separated by a black filled cell which I use to separate their data.

Women consists of Jane and Mary formatted the same as "men"

In the "men" worksheet ..If the current month is AUG find previous
month
(i.e. JUL) in row 3 in columns F thru Q. [note the months are text in
row3
and formatted as Jan Feb Mar Apr May etc.]. [The real "previous
month"
date
formula is located in Cell A3 and is formatted "mmm"] Then in column
A
find
"John" and count all cells in the previous month with bright green
background
and stop counting or looking for green when you hit the first black
background in that same column, then in COUNTSUMMARY sheet look for
the
row
labeled "John" in column A and place the total count in COUNTSUMMARY
in
column H in the same row as "John" and change background of that cell
to
bright green. If the count is 0 then 0 IS NOT displayed and the
background
does not change color.

Then follow that same procedure but now count red cells and follow
same
procedures as above but put in column P.

Then I need to do the same thing for "Rob".

Then I need to go to the "Women" spreadsheet and do the exact same
thing
for
"Jane" and "Mary" and have the count information placed in the correct
cell
in SHEET1.

Any ideas on how to do this without having long winded VBA code?
Thanks
in
advance. I hope I made sense.