CONDITIONAL FORMAT

J

John

I need to use three colors with multiple conditions;
green = 5.6-6.0 or 6.1-7.0
Yellow= <700 or >900
Red= 1.1-3.8 or 2.0-5.9 or 5.0-6.9
They are in rows; B12 and row C13 and rowD14.
I'm ok with the cells, but not formulas. I appreciate you in advance!

Thanks, Heat.
 
B

Bob Phillips

Select all of B12, C13 and D14

=OR(AND(B12>=5.6,B12<=6),AND(B12>=6.1,B12<=7.0))

=OR(B12<700,B12>900)

=OR(AND(B12>=1.1,B12<=3.8),AND(B12>=2.0,B12<=6.9))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Richard Buttrey

I need to use three colors with multiple conditions;
green = 5.6-6.0 or 6.1-7.0
Yellow= <700 or >900
Red= 1.1-3.8 or 2.0-5.9 or 5.0-6.9
They are in rows; B12 and row C13 and rowD14.
I'm ok with the cells, but not formulas. I appreciate you in advance!

Thanks, Heat.
Click to expand...

Is 6.1 (for instance) green or red? The conditions seem to overlap.

Rgds

Richard Buttrey
__
 
J

John

Bob, we shouldn't be working on Thanksgiving, however, thanks! It worked, but
I needed to replace B with an A otherwise is colored one cell over. Thanks
again!
HEAT.
 
B

Bob Phillips

That's okay, we don't do Thanksgiving over here. I don't understand why you
had to change B to A from your original post, but I am sure that you do, and
that is all that matters.

Regards

Bob
 
J

John

Hey Bob,
Here's anotherone for you. This worked great;
=OR(AND(B12>=5.6,B12<=6),AND(B12>=6.1,B12<=7.0))

However, I have another request. I want row 26b to 26z to stay clear until I
enter a zero in the cell. Then I want the cell to turn green. ONLY WHEN I PUT
A "0 (ZERO)" NOT WHEN IT'S CLEAR" Any ideas?
Your help is apprecitive.

HEAT
 
S

SteveG

Try

=AND(B26<>"",B26=0)

This can be copied to cells C26:Z26 using the format painter. If you
want the reference in the condition to change to each cell, be sure to
remove the $'s from the formula. I here you on the Thanksgiving thing.
Luckily, half a day for me.



Cheers,

Steve
 
J

John

HERE'S ANOTHER ONE FOR YOUR PROS.
I want the respective condition with each cell in row 31 to change to yellow
when it's 26% to 35% of the respective condition with each cell in row 19. If
the cell in row 31 is over 36% turn the cell red.
i.e.
b19 1000
b31 1300 (this should turn yellow)

c19 1000
c31 2000 (this should turn red)

Basically 1000 * 26%-35%. help me on this please.

Thanksgiving a lot!!
 
B

Bob Phillips

I presume that you mean over?

Select cells in row 31, starting with say column B, CF with formula of

=(B31-B$19)/B$19>35% for red

and

=(B31-B$19)/B$19>26% for yellow

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

SteveG

If you want to have it color yellow if the % is between 26% and 35%
(including 26% & 35%).

=AND((B31-B$19)/B$19>=26%,(B31-B$19)/B$19<=35%)

The formula for red that Bob gave stays the same. This will cause the
cell not to fill with any color if your % falls between 35% and 36%.

Cheers,

Steve
 
J

John

Bob,
Thanks for your reply, however, I can't get it to work. The yellow
superceeds the red and vice versa, if I take the yellow formual out.

HEAT
 
J

John

Steve,
To spice up things within my conditions I want to add one extra calculation.
The formula is great, however, I need it Row 19 to be multiplied by 1000
(onethousand) before row 31 calculates the percentage increase. Any ideas?
for instance;
=AND((B31-B$19)/B$19*1000>=26%,(B31-B$19)/B$19*1000<=35%)
 
Top