Need help with complicated use of count background color of Cell

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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

I actually have 6 colors in all that I am needing a count on, all in
different columns. I am unsure of what options I have regarding conditional
formatting although I am quite familiar with it. I'm not sure I understand
what you are saying though with my overall limited knowledge. Since
CountSummary is counting green in one column and red in another column
whatever your idea is just may work. Would you elaborate on what you are
thinking? Thanks so much. You guys are so helpful it is absurd for us
needing help. Your advice is absolutely invaluable. Do you ever get more
than a Thank you?

"Tom Ogilvy" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

Jane,

See my reply in your other thread.

Columns(5 + (Month(Date) - 1)).Select will select to the column with last
month's name in row 3

Henry


"Jane" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

If looking for John/Jane is apropriate, then I look for all 4 names to
determine the start row. For John, I use the row before Rob to determine
the end row. Similar for Jane. For Rob and Mary, I use the last row in the
used range. Anyway, it worked for me based on my understanding of your
description.

Sub CountColors()
Dim shts(1 To 2) As Worksheet
Dim v(1 To 2, 1 To 2) As String
Dim i As Long, j As Long, k As Long
Dim sh As Worksheet
Dim mnth As String
Dim dte As Date
Dim col As Variant, rw As Variant
Dim rw1 As Variant, rw2 As Variant
Dim tot As Long

v(1, 1) = "John"
v(1, 2) = "Rob"
v(2, 1) = "Jane"
v(2, 2) = "Mary"
Set sh = Worksheets("CountSummary")
Set shts(1) = Worksheets("Men")
Set shts(2) = Worksheets("Women")
dte = DateSerial(Year(Date), Month(Date) - 1, Day(Date))
mnth = Format(dte, "mmm")
For i = 1 To 2
col = Application.Match(mnth, shts(i).Range("F3:Q3"), 0)
col = col + 5
For j = 1 To 2
tot = 0
rw = Application.Match(v(i, j), shts(i).Columns(1), 0)
If j = 1 Then
rw1 = Application.Match(v(i, 2), shts(i).Columns(1), 0)
Else
rw1 = shts(i).UsedRange.Rows(shts(i).UsedRange.Rows.Count).Row + 1
End If
tot = 0
For k = rw To rw1 - 1
If shts(i).Cells(k, col).Interior.ColorIndex = 4 Then
tot = tot + 1
End If
Next
rw2 = Application.Match(v(i, j), sh.Columns(1), 0)
If tot > 0 Then
sh.Cells(rw2, "H").Value = tot
sh.Cells(rw2, "H").Interior.ColorIndex = 4
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy

"Jane" <[email protected]> wrote in message
news:[email protected]...
> I actually have 6 colors in all that I am needing a count on, all in
> different columns. I am unsure of what options I have regarding
conditional
> formatting although I am quite familiar with it. I'm not sure I understand
> what you are saying though with my overall limited knowledge. Since
> CountSummary is counting green in one column and red in another column
> whatever your idea is just may work. Would you elaborate on what you are
> thinking? Thanks so much. You guys are so helpful it is absurd for us
> needing help. Your advice is absolutely invaluable. Do you ever get more
> than a Thank you?
>
> "Tom Ogilvy" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >

Thanks again Henry. I read what you wrote in my other thread and I know how
to apply it but I don't even know how to start this vba. See I write a
report each month, say in Aug, based on the numbers from the previous month,
Jul. I have a lot of vba already set up to handle other things that I have to
work with my current set up. I'm guessing sumproduct would some how be
involved since I've used that before but I just have not learned enough yet
to set this up. Thanks.

"Henry" wrote:

> Jane,
>
> See my reply in your other thread.
>
> Columns(5 + (Month(Date) - 1)).Select will select to the column with last
> month's name in row 3
>
> Henry
>
>
> "Jane" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >