looking for a number in array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

arturo.cohen@gmail.com - 12 Jun 2006 13:28 GMT

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?

Reply to this Message

Dave Peterson - 12 Jun 2006 13:44 GMT

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

> 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
[quoted text clipped - 6 lines]
>
> How can i workaround of this?

Signature

Dave Peterson

Reply to this Message

ArturoCohen - 12 Jun 2006 14:27 GMT

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

> I put my list in B1:B5 and my cell with conditional formatting in A1.
>
[quoted text clipped - 8 lines]
> and the formula will be more like:
> =Countif(myList,a1)=0

Reply to this Message