Guys, I'm a little unsure how to complete this one. In the example below I
am trying to format cell A1 based on 3 criteria ,,, if cell C1 is less than
80% colour A1 red, if C1 is greater than 80% but less than 90% colour A1
yellow and if C1 is greater than 90% colour A1 green .... the Red and Green I
can get using the Formula Is box in the conditional format function ,, but
how do I get the "between" to work ??


A B C
1 13% 19% 68%

There is a hierarchy with Conditional Formatting - the first condition
takes precedence. So, make your first formula:

=C190% (set to green)

Second condition:

=C180% (set to yellow)

Third condition:

=AND(C1<80%,C1<"") (set to red)

The last condition avoids the colour if the cell is empty.

You could use AND to specify a range of values, but by setting the
conditions in this order you can avoid that.

Hope this helps.

Pete

On Jul 23, 11:23*am, John Moore
wrote:
Guys, I'm a *little unsure how to complete this one. In the example below I
am trying to format cell A1 based on 3 criteria ,,, if cell C1 is less than
80% colour A1 red, if C1 is greater than 80% but less than 90% colour A1
yellow and if C1 is greater than 90% colour A1 green .... the Red and Green I
can get using the Formula Is box in the conditional format function ,, but
how do I get the "between" to work ??

* * * * A * * * * B * * * *C
1 * * 13% * *19% * 68%

Thanks Pete ,, works well .... saves me some time ,, appreciate it

"Pete_UK" wrote:

There is a hierarchy with Conditional Formatting - the first condition
takes precedence. So, make your first formula:

=C190% (set to green)

Second condition:

=C180% (set to yellow)

Third condition:

=AND(C1<80%,C1<"") (set to red)

The last condition avoids the colour if the cell is empty.

You could use AND to specify a range of values, but by setting the
conditions in this order you can avoid that.

Hope this helps.

Pete

On Jul 23, 11:23 am, John Moore
wrote:
Guys, I'm a little unsure how to complete this one. In the example below I
am trying to format cell A1 based on 3 criteria ,,, if cell C1 is less than
80% colour A1 red, if C1 is greater than 80% but less than 90% colour A1
yellow and if C1 is greater than 90% colour A1 green .... the Red and Green I
can get using the Formula Is box in the conditional format function ,, but
how do I get the "between" to work ??

A B C
1 13% 19% 68%

Hey John,

There is a problem in your description, what if C1 is exactly 80% or
90%? As described they would return no color.

Assuming you would like the crossovers to start at 80% and 90%, also
assuming your cell C1 is formatted as percentage, I believe your
Conditional Formats could be:

1st condition: =$C$1<0.8
2nd condition: =AND($C$10.79,$C$1<0.9)
3rd condition: =$C$10.89

Or to simplify, you could try:

1st condition: =$C$1<0.8
2nd condition: =$C$1<0.9
3rd condition: =$C$10.89

Since the first condition eliminates the need to restrict the lower
portion of the 2nd condition, the AND($C$10.79,_____) is not needed.
They both work for me, they should work for you.

HTH

-Minitman

On Wed, 23 Jul 2008 03:23:03 -0700, John Moore
wrote:

Guys, I'm a little unsure how to complete this one. In the example below I
am trying to format cell A1 based on 3 criteria ,,, if cell C1 is less than
80% colour A1 red, if C1 is greater than 80% but less than 90% colour A1
yellow and if C1 is greater than 90% colour A1 green .... the Red and Green I
can get using the Formula Is box in the conditional format function ,, but
how do I get the "between" to work ??


A B C
1 13% 19% 68%

You're welcome, John - thanks for feeding back.

Pete

On Jul 23, 12:08*pm, John Moore
wrote:
Thanks Pete ,, works well .... saves me some time ,, appreciate it