Need help with complicated use of count background color of Cell

[Guest]

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.

 

[Tom Ogilvy]

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)

 

[Guest]

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?

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

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.

 

[Henry]

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

 

[Tom Ogilvy]

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

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?

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

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.

 

[Guest]

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.

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

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.

 

[Mike Fogleman]

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

 

[Guest]

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.

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

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.

 

[Tom Ogilvy]

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

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.

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

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.