How are your cells colored? Do you actually change the background color or
do you use conditional formatting. If using conditional formatting, why not
build a formuila in CountSummary that uses the same condition. If the
separation of John and Rob is dynamic, is there nothing in the data which
can be used to determine John and Rob on a row by row basis. (even if there
is a cell at the top of each section with their name)

--
Regards,
Tom Ogilvy

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