looking for a number in array

[arturo.cohen]

I Would like to make a cell change its color when the number entered
does not mach any of the numbers in a specified array of numbers (which
is in a separate sheet - sheet3 -- ) .

I used Vlookup to serch in the array, and used conditional formating
(when cell value not equal to..) but when the number is not in the
list, the cell still doesnt change its colors even when the another
cell is #N/A

How can i workaround of this?

 

[Dave Peterson]

I put my list in B1:B5 and my cell with conditional formatting in A1.

I used this formula in the Conditional Formatting formula:
=COUNTIF(B1:B5,A1)=0

Since your list is in a different worksheet, you'll have to name that list
range.

Insert|Name|Define

and the formula will be more like:
=Countif(myList,a1)=0

 

[ArturoCohen]

wow, Dave, really, thanks for the help, i thought it was a little more
complicated, but seems like it isnt. thanks so much for the help,
worked perfect!!

Arturo Cohen