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 -