Highlighting Cells with Conditional Formating Formulas

**sighlent1** · 04-07-2010, 10:00 AM

Hey,

I'm looking to highlight cells using conditional formatting formulas

I have 3 items (1, 2 & 3) and each item has a range (100-200, 250-350, 400-500) respectively

When typing in cell A1 either "1", "2" or "3" and then any value in A2, if the value is outside the given ranges, I'd like A2 to be highlighted in Red

Is there a formula that I could use to make this work?

I tried using IF AND statements, but I'm just learning conditional formatting so I wasn't able to get it to work

Thank you and I appreciate your time

Bob

**NBVC** · 04-07-2010, 10:08 AM

Try this Conditional Formula on A2:

=AND(A1<>"",A2<>"",OR(AND(A1=1,OR(A2<100,A2>200)),AND(A1=2,OR(A2<250,A2>350)),AND(A1=3,OR(A2<400,A2>500))))

**sighlent1** · 04-07-2010, 10:21 AM

That's EXACTLY what I was looking for

Thank you SOOOO much, I really appreciate it!!!!!

Bob

**sighlent1** · 04-07-2010, 10:29 AM

Just curious, what does this part of the code mean?:

A1<>"",A2<>""

**NBVC** · 04-07-2010, 10:33 AM

Just ensuring that A1 and A2 are not blank first....

If you enter a value in A1 and A2 is blank, it colours the cell Red if those aren't there...

I suppose you don't really need the A1<>"" part... but doesn't hurt to have the extra measure of safety.