Hello Excel Gurus,
I have a spreadsheet Columns A:Q with any number of rows. I have auto sums
in the spreadsheet.
I would like to be able to run a macro to do the following:
look at column A and when the row contains the word total
Bold that row for Column A:Q
and for that row Look at Columns F:Q and if the data is < 90% then color
that column Green
If that column is than 110% color that column Yellow.
So each of the Totals columns F:Q would be either yellow or green.
I am using 2007.
Many thanks in advance

Here is some air code that might help get you started. Totally off the
top....

Sub BoldMe()

Dim rng As Excel.Range
Dim cell As Excel.Range

Set rng = Worksheets("mySheet").Range("A1",
Worksheets("mySheet").Range("A65536").End(xlUp))

For Each cell in rng.SpecialCells(xlCellTypeConstants,2) 'text values
only
If UCase$(cell.Value) = "TOTAL" Then
Range(cell, Range(cell.Offset(0,16))).Font.Bold = True
End If
Next cell

End Sub

Even faster is to use the .Find method on a range object. See
http://www.ozgrid.com/VBA/VBALoops.htm for examples.

Does that help?

HTH,
JP


On Apr 2, 4:31*pm, CB wrote:
Hello Excel Gurus,
I have a spreadsheet Columns A:Q with any number of rows. *I have auto sums
in the spreadsheet. *
I would like to be able to run a macro to do the following:
look at column A and when the row contains the word total
Bold that row for Column A:Q
and for that row Look at Columns F:Q and if the data is < 90% then color
that column Green
If that column is than 110% color that column Yellow.
So each of the Totals columns F:Q would be either yellow or green.
I am using 2007.
Many thanks in advance

JP,
Thanks but I couldn't get this to work. Let me be a little more detailed
on what I need. It boils down to producing an "EYE" chart in a spreadsheet
for my boss.

Below is my data:
Col A B C
Row Sales Person Apr 08 May 08
1 TP 50% 75%
2 TP Total 50% 75%
3 AG 10% 30%
4 AG 40% 60%
5 AG Total 50% 90%

Columns A:C will be consistently extracted each week, however the number of
rows and sales people can change drastically.
I need something that can loop through the entire sheet keying first on the
rows in column A that contain the word "Total" then looking at B:C on the
same row and if that data in b and c is ,90% color fill green, if data in b
and c is 110% then color fill yellow. I want to leave the text in the
cells as well.
Any ideas?

"JP" wrote:

Here is some air code that might help get you started. Totally off the
top....

Sub BoldMe()

Dim rng As Excel.Range
Dim cell As Excel.Range

Set rng = Worksheets("mySheet").Range("A1",
Worksheets("mySheet").Range("A65536").End(xlUp))

For Each cell in rng.SpecialCells(xlCellTypeConstants,2) 'text values
only
If UCase$(cell.Value) = "TOTAL" Then
Range(cell, Range(cell.Offset(0,16))).Font.Bold = True
End If
Next cell

End Sub

Even faster is to use the .Find method on a range object. See
http://www.ozgrid.com/VBA/VBALoops.htm for examples.

Does that help?

HTH,
JP


On Apr 2, 4:31 pm, CB wrote:
Hello Excel Gurus,
I have a spreadsheet Columns A:Q with any number of rows. I have auto sums
in the spreadsheet.
I would like to be able to run a macro to do the following:
look at column A and when the row contains the word total
Bold that row for Column A:Q
and for that row Look at Columns F:Q and if the data is < 90% then color
that column Green
If that column is than 110% color that column Yellow.
So each of the Totals columns F:Q would be either yellow or green.
I am using 2007.
Many thanks in advance

I did find one error in the code and fixed it. But can you explain in
more detail what you mean by "then looking at B:C on the same row and
if that data in b and c is ,90% color fill green, if data in b and c
is 110% then color fill yellow."

Do you mean if the TOTAL of B2 & C2 is 90%, color the entire row
green? Or if EITHER of them are 90%?

And if the TOTAL of B2 & C2 is greater than 110%, color the entire row
yellow? Or if EITHER of them are 110%?

Just trying to understand exactly what you need so I can write the
appropriate code.


Thx,
JP



On Apr 3, 2:56*pm, CB wrote:
JP,
Thanks but I *couldn't get this to work. *Let me be a little more detailed
on what I need. *It boils down to producing an "EYE" chart in a spreadsheet
for my boss.

Below is my data:
Col * * * A * * * * * * * * * *B * * * * * * * *C
Row * Sales Person * * *Apr 08 *May 08 *
1 * * * * TP * * * * * * * * * *50% * * * * * 75% * * * * * * * * * * * * * * *
2 * * * TP Total * * * *50% * * * 75% *
3 * * * *AG * * * * * * * * * * 10% * * * 30% * * * * * * * * *
4 * * * *AG * * * * * * * * * * 40% * * * 60% * * * * * * * * *
5 * * * AG Total * * * *50% * * * * * *90%

Columns A:C will be consistently extracted each week, however the number of
rows and sales people can change drastically.
I need something that can loop through the entire sheet keying first on the
rows in column A that contain the word "Total" then looking at B:C on the
same row and if that data in b and c is ,90% color fill green, if data in b
and c is * *110% then color fill yellow. *I want to leave the text in the
cells as well.
Any ideas?



"JP" wrote:
Here is some air code that might help get you started. Totally off the
top....

Sub BoldMe()

Dim rng As Excel.Range
Dim cell As Excel.Range

Set rng = Worksheets("mySheet").Range("A1",
Worksheets("mySheet").Range("A65536").End(xlUp))

For Each cell in rng.SpecialCells(xlCellTypeConstants,2) 'text values
only
* * If UCase$(cell.Value) = "TOTAL" Then
* * * * Range(cell, Range(cell.Offset(0,16))).Font.Bold = True
* * End If
Next cell

End Sub

Even faster is to use the .Find method on a range object. See
http://www.ozgrid.com/VBA/VBALoops.htmfor examples.

Does that help?

HTH,
JP

On Apr 2, 4:31 pm, CB wrote:
Hello Excel Gurus,
I have a spreadsheet Columns A:Q with any number of rows. *I have auto sums
in the spreadsheet. *
I would like to be able to run a macro to do the following:
look at column A and when the row contains the word total
Bold that row for Column A:Q
and for that row Look at Columns F:Q and if the data is < 90% then color
that column Green
If that column is than 110% color that column Yellow.
So each of the Totals columns F:Q would be either yellow or green.
I am using 2007.
Many thanks in advance- Hide quoted text -

- Show quoted text -

JP,
Thanks so much for your time and help.
Let me see if I can be more specific. The spreadsheet has subtotals.
Hence, I want to color code the totals rows for Col B and C if they meet the
criteria, if not no color.
Given the example below,

Row one would be skipped because A1 does not contain the word "Total".

A2 contains the word "Total", Therefore B2 is less than 90% and would color
fill with green and still display the text 50%. C2 is also less than 90 %
and would color fill with green and still display the text 75%.
If B2 or C2 were greater than 110% then they would color fill with yellow
displaying the text in their respective cells.
If B2 or C2 contained data between 90% and 110%, then no action would be
taken.

Thanks again for your help.
Cb

"JP" wrote:

I did find one error in the code and fixed it. But can you explain in
more detail what you mean by "then looking at B:C on the same row and
if that data in b and c is ,90% color fill green, if data in b and c
is 110% then color fill yellow."

Do you mean if the TOTAL of B2 & C2 is 90%, color the entire row
green? Or if EITHER of them are 90%?

And if the TOTAL of B2 & C2 is greater than 110%, color the entire row
yellow? Or if EITHER of them are 110%?

Just trying to understand exactly what you need so I can write the
appropriate code.


Thx,
JP



On Apr 3, 2:56 pm, CB wrote:
JP,
Thanks but I couldn't get this to work. Let me be a little more detailed
on what I need. It boils down to producing an "EYE" chart in a spreadsheet
for my boss.

Below is my data:
Col A B C
Row Sales Person Apr 08 May 08
1 TP 50% 75%
2 TP Total 50% 75%
3 AG 10% 30%
4 AG 40% 60%
5 AG Total 50% 90%

Columns A:C will be consistently extracted each week, however the number of
rows and sales people can change drastically.
I need something that can loop through the entire sheet keying first on the
rows in column A that contain the word "Total" then looking at B:C on the
same row and if that data in b and c is ,90% color fill green, if data in b
and c is 110% then color fill yellow. I want to leave the text in the
cells as well.
Any ideas?



"JP" wrote:
Here is some air code that might help get you started. Totally off the
top....

Sub BoldMe()

Dim rng As Excel.Range
Dim cell As Excel.Range

Set rng = Worksheets("mySheet").Range("A1",
Worksheets("mySheet").Range("A65536").End(xlUp))

For Each cell in rng.SpecialCells(xlCellTypeConstants,2) 'text values
only
If UCase$(cell.Value) = "TOTAL" Then
Range(cell, Range(cell.Offset(0,16))).Font.Bold = True
End If
Next cell

End Sub

Even faster is to use the .Find method on a range object. See
http://www.ozgrid.com/VBA/VBALoops.htmfor examples.

Does that help?

HTH,
JP

On Apr 2, 4:31 pm, CB wrote:
Hello Excel Gurus,
I have a spreadsheet Columns A:Q with any number of rows. I have auto sums
in the spreadsheet.
I would like to be able to run a macro to do the following:
look at column A and when the row contains the word total
Bold that row for Column A:Q
and for that row Look at Columns F:Q and if the data is < 90% then color
that column Green
If that column is than 110% color that column Yellow.
So each of the Totals columns F:Q would be either yellow or green.
I am using 2007.
Many thanks in advance- Hide quoted text -

- Show quoted text -

I tested this code and it works. Paste the code in a standard module
and test it out first by stepping through the code one line at a time.
Make sure you change "mySheet" to the name of the actual worksheet you
are using this code on. I recommend you use a copy of your data to
test it first.

Option Explicit

Sub TotalCheck()

Dim rng As Excel.Range
Dim cell As Excel.Range
Dim ColBVal As String
Dim ColCVal As String

Set rng = Worksheets("mySheet").Range("A1", _
Worksheets("mySheet").Range("A65536").End(xlUp))

For Each cell In rng.SpecialCells _
(xlCellTypeConstants, 2) 'text values

ColBVal = cell.Offset(0, 1).Value
ColCVal = cell.Offset(0, 2).Value

If UCase$(cell.Value) = "TOTAL" Then
'check col B
Select Case CDbl(ColBVal)
Case Is < 0.9
'green
cell.Offset(0, 1).Interior.ColorIndex = 4
Case Is 1.1
'yellow
cell.Offset(0, 1).Interior.ColorIndex = 6
End Select

'check col C
Select Case CDbl(ColCVal)
Case Is < 0.9
'green
cell.Offset(0, 2).Interior.ColorIndex = 4
Case Is 1.1
'yellow
cell.Offset(0, 2).Interior.ColorIndex = 6
End Select
End If
Next cell

End Sub


On Apr 4, 8:39*am, CB wrote:
JP,
Thanks so much for your time and help.
Let me see if I can be more specific. *The spreadsheet has subtotals. *
Hence, I want to color code the totals rows for Col B and C if they meet the
criteria, if not no color.
Given the example below,

Row one would be skipped because A1 does not contain the word "Total".

A2 contains the word "Total", *Therefore B2 is less than 90% and would color
fill with green and still display the text 50%. *C2 is also less than 90 %
and would color fill with green and still display the text 75%.
If B2 or C2 were greater than 110% then they would color fill with yellow
displaying the text in their respective cells.
If B2 or C2 contained data between 90% and 110%, then no action would be
taken.

Thanks again for your help.
Cb

JP,
Thanks for the help. I am on my way out of town now, but will definitely
try this first thing next week. Again Thanks so much.
Have a good weekend.
CB

"JP" wrote:

I tested this code and it works. Paste the code in a standard module
and test it out first by stepping through the code one line at a time.
Make sure you change "mySheet" to the name of the actual worksheet you
are using this code on. I recommend you use a copy of your data to
test it first.

Option Explicit

Sub TotalCheck()

Dim rng As Excel.Range
Dim cell As Excel.Range
Dim ColBVal As String
Dim ColCVal As String

Set rng = Worksheets("mySheet").Range("A1", _
Worksheets("mySheet").Range("A65536").End(xlUp))

For Each cell In rng.SpecialCells _
(xlCellTypeConstants, 2) 'text values

ColBVal = cell.Offset(0, 1).Value
ColCVal = cell.Offset(0, 2).Value

If UCase$(cell.Value) = "TOTAL" Then
'check col B
Select Case CDbl(ColBVal)
Case Is < 0.9
'green
cell.Offset(0, 1).Interior.ColorIndex = 4
Case Is 1.1
'yellow
cell.Offset(0, 1).Interior.ColorIndex = 6
End Select

'check col C
Select Case CDbl(ColCVal)
Case Is < 0.9
'green
cell.Offset(0, 2).Interior.ColorIndex = 4
Case Is 1.1
'yellow
cell.Offset(0, 2).Interior.ColorIndex = 6
End Select
End If
Next cell

End Sub


On Apr 4, 8:39 am, CB wrote:
JP,
Thanks so much for your time and help.
Let me see if I can be more specific. The spreadsheet has subtotals.
Hence, I want to color code the totals rows for Col B and C if they meet the
criteria, if not no color.
Given the example below,

Row one would be skipped because A1 does not contain the word "Total".

A2 contains the word "Total", Therefore B2 is less than 90% and would color
fill with green and still display the text 50%. C2 is also less than 90 %
and would color fill with green and still display the text 75%.
If B2 or C2 were greater than 110% then they would color fill with yellow
displaying the text in their respective cells.
If B2 or C2 contained data between 90% and 110%, then no action would be
taken.

Thanks again for your help.
Cb