CONDITIONAL FORMAT

[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.

[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)


"John" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Richard Buttrey]

On Thu, 24 Nov 2005 06:20:07 -0800, "John"
<[email protected]> wrote:

>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.

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

Rgds

Richard Buttrey
__

[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.

"Bob Phillips" wrote:

> 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)
>
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[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


"John" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> >
> > "John" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

[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

"Bob Phillips" wrote:

> 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
>
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> > "Bob Phillips" wrote:
> >
> > > 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)
> > >
> > >
> > > "John" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
>
>

[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

[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!!

"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=487940
>
>

[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)


"John" <[email protected]> wrote in message
news:[email protected]...
> 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!!
>
> "SteveG" wrote:
>
> >
> > 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
> >
> >
> > --
> > SteveG
> > ------------------------------------------------------------------------
> > SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
> > View this thread:
http://www.excelforum.com/showthread...hreadid=487940
> >
> >

[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

[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

"Bob Phillips" wrote:

> 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)
>
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
> > 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!!
> >
> > "SteveG" wrote:
> >
> > >
> > > 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
> > >
> > >
> > > --
> > > SteveG
> > > ------------------------------------------------------------------------
> > > SteveG's Profile:
> http://www.excelforum.com/member.php...fo&userid=7571
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=487940
> > >
> > >
>
>
>

[John]

SWEET! Thank you for the assistance Steve. UDAMAN!

"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=487940
>
>

[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%)

"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=487940
>
>