Conditional formating that ignores text

fuadramsey

New Member
Joined
Jun 12, 2006
Messages
9
I want all cells in a column to turn red if they are not within this price range 69.00-250.00 (not to include blank cell or text). I can do this with conditional formating but it changes text I enter in that column red too.

Here is what I have:
condition 1: Cell Value is between 1 and 68.99 (highlight red)
condition 2: Cell Value is greater than 250.00 (highlight red)

This works, but letters (I need to enter codes sometimes) I enter are highlighted red.

Help!?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
Instead of Cell Value Is, use:

Condition2: Formula is =AND(ISNUMBER(A1),A1>250) where A1 is the cell you are formatting.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Actually, seeing as you're formatting *both* conditions as red, why not make it *one* condition and free up the other 2 spots?

This seems to work for me:
Condition1: Formula is | =ISNUMBER(A1)*OR((A1>=1)*(A1<=68.99),A1>250)
Change A1 to whatever cell you need it to refer to.

This should highlight the cell only if the value is a number between 1-68.99 or over 250.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,038
Members
410,583
Latest member
gazz57
Top