Conditional formatting formula for value between two other values

**ACurtis802** · 01-10-2009, 08:20 PM

Hi everyone,

I am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple.

There are 3 conditions I would like to apply to cell B2:

If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red.

If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange.

If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow.

I am sure I am missing something quite simple here. Can anyone point me in the right direction?

Thanks,
ACurtis802

**Tony M** · 01-10-2009, 08:55 PM

go to formatting/conditional formatting. Then in the drop down box in the top left Click "formula is" then type a logical if formula based on your criteria in the box to the right. Then add the next condition,

Hope that helps

**mudraker** · 01-10-2009, 08:58 PM

Use Tony M instructions with these formulas

For condition 1
=AND(L2>=21,L2<=24)

For condition 2
=AND(L2>=12,L2<=20)

For condition 3
=AND(L2>=8,L2<=11)

**Tony M** · 01-10-2009, 09:06 PM

Just for clarity's sake

Condition 1: =IF(I2<=24,1," ")+IF(I2>=21,1," ")

Then go format/paterns then click red

Conditon 2: =IF(I2<=20,1," ")+IF(I2>=12,1," ")

Then go format/paterns then click orange

Condition 3: =IF(I2<=11,1," ")+IF(I2>=8,1," ")

Then go format/paterns then click yellow

**Tony M** · 01-10-2009, 09:08 PM

Thanks Mudraker, much simpler formula than mine

**ACurtis802** · 01-11-2009, 05:57 PM

Thank you both very much.

The value in cell I2 looks like a number, but it is actually evaluating to text because of a workaround I am using to exceed the 7 nested IF argument limit.

As such, these formulae do not do the trick exactly. However, using your logic, I am now asking the conditional formatting formula to evaluate based on instances of the text string, e.g., "24" vs 24, and everything is working perfectly.

Maybe a little clumsy, but problem solved! :-)

**JLHalliday1** · 12-03-2013, 08:58 PM

FABULOUSLY HELPFUL!!!

I had never placed a between formula in the conditional formatting - this worked wonderfully, thank you!

Originally Posted by mudraker

Use Tony M instructions with these formulas

For condition 1
=AND(L2>=21,L2<=24)

For condition 2
=AND(L2>=12,L2<=20)

For condition 3
=AND(L2>=8,L2<=11)

**TOMS2825** · 06-01-2014, 12:32 PM

I Have a similar issue but with percentage and I am using a cell for the Constance value.

=OR(B2>B11,B2=B11) (100% or greater)GREEN

=OR(B2<0.99%,B2>0.9%) (90 to 99%)ORANGE

=OR(B2<0.89%,B2>0.75%) (75 to 89%)YELLOW

=B2<0.74% (74% and Below)RED

Cell B11 holds the Constance Value and Cell B2 will very and change color depending on the percentage of B11

**TOMS2825** · 06-01-2014, 02:37 PM

I am using this with 6 extra cells to give me the percent value

=OR(B2>B11,B2=B11)

=AND($B$2<$B$13,$B$2>$B$14)

=AND($B$2<$B$15,$B$2>$B$16)

=AND($B$2<$B$17)

A1 B1
A2 17
A3 82
A4 97
A5 0.98
A6 2
A7 11.8%
A8 9
A9 12.8%
A10 26.7%
A11 22

21.78
19.8
19.58
17.6
17.38
16.5

IT WORKS BUT IS THERE A SIMPLER WAY?

**Pepe Le Mokko** · 06-01-2014, 03:07 PM

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.