MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional Formatting


Posted by Bryan on May 10, 2001 2:11 PM

I'm familiar with conditional formatting but I can't figure out how to conditionally format a cell if its value (text) is NOT equal to any of the values (all text in this case) in a range of cells in another column ($U$2:$U$10). Thanks in advance.


Posted by IML on May 10, 2001 2:21 PM

You could use conditional formatting with the following formula
=IF(ISNA(VLOOKUP(A1,list,1,FALSE)),1,0)=1
list is your list of items you want formatted normally.

good luck

Posted by Barrie Davidson on May 10, 2001 2:24 PM

Try this in the condition (using "formula is", not "cell value is"), assuming you are formatting cell A1.
=ISNA(VLOOKUP(A1,$U$2:$U$10,1,0))

Barrie

Posted by Dave Hawley on May 10, 2001 2:35 PM


Hi Bryan

if it's only text you are interested in then you could use:
=IF(AND(NOT(ISNUMBER(A1)),COUNTIF($U$2:$U$10,A1)=0))

There are some examples of Conditional Formatting and Data Validation that may be of use to you on my Website under "Handy Hints"


Dave
OzGrid Business Applications

Posted by Bryan on May 10, 2001 2:41 PM

Thank you all very much. Barrie, I going with your solution but they were all quite educational.

Posted by Bryan on May 10, 2001 2:41 PM

Thank you all very much. Barrie, I am going with your solution but they were all quite educational.