Help With Conditional formatting

[CB]

I am using 2007.
My spreadsheet contains sales figures for the sales staff and is auto summed
for each sales person.
I need to conditional format G:K based on the word Total in Col A and The
cell values in Cols G:K.
So that for each row in col A that contains the word "Total", Excel will
format G:K based on their value.
If A contains "Total" set conditional fomat of G:K as follows:
If G:K < 100 Fill Color Green
If G:K > 150 fill color Yellow
Can someone please help?

 

[muddan madhu]

use sumif condition
=sumif(G:K,"<100) & =sumif(G:K,">150")

 

[curious engineer]

Try the following....

This is based on the conditional formatting for one particular cell (G1),
but you can copy this formatting to all other cells in H, I, J, and K.

Instead of choosing Cell Value Is in the drop down menu, choose Formula Is
the formula should look like =AND($A1="Total",G1>150), then change the
format to fill yellow.

There has to be another for the green fill, add another condition and use
the same formual except changing G1>150 to G1<100 and make the fill green.

You can then copy and paste special this conditional format to any cell.


Hope this helps!!

 

[Bob Umlas, Excel MVP]

Select G:K
Conditional Formatting/Manage Rules/New Rule
Select "Use a formula to determine which cells to format"
enter =AND($A1="TOTAL",G1>150)
click Format, give it yellow fill.
Click OK, then click New Rule
enter =AND($A1="TOTAL",G1<100)
click Format, give it green fill.
You're done.
Note - the "$" is important. Also assumes that G1 is the ACTIVE cell while
applying these rules.
Bob Umlas
Excel MVP

 

[CB]

Bob,
Thanks for your response. I tried this and discovered that I left out some
information. I need to use this if Column A contained the text "Austin
Total", "Dallas Total"....etc. Is there a way to tell it for $A1 for rows
Containing "Total"?
Thanks,
CB

 

[Bob Umlas, Excel MVP]

Sure. change formulas to:
=AND(RIGHT($A1,5)="Total",G1>150)
=AND(RIGHT($A1,5)="Total",G1<100)

 

[CB]

Bob,
This works great! Thanks so much.
CB

Sure. change formulas to:
=AND(RIGHT($A1,5)="Total",G1>150)
=AND(RIGHT($A1,5)="Total",G1<100)