when typing to a cell check if this number exist in a range of cells (in sheet 2)

I need help to this : When i type a number to a cell and press enter , i want to check if this number exist in a range of cells (in sheet 2) , and if exists , excel show me a message. Actually i use it for my dvd club. Number is the client code. When i writte 50 in a cell , i need from excel to check if this client own me money , and show me some message..
Thnaks

Perhaps you can use Data Validation for this... no programming

Select the list in Sheet2 and name it something like List through Insert|Name|Define

Then go to the input cell in Sheet1 and go to Data|Validation

Select Custom from the Allow options and enter formula:

=ISNA(MATCH(A1,List,0))

Where A1 is the input cell

Then select the Error Alert Tab and enter a Title and message you want to appear.

Click Ok. Test it.

You could also use VLOOKUP to check the amount owed if the data is stored in the same table as the IDs

Originally Posted by gtserkou

thank u for your answers

however the check must take place from a range of input cells to a range of comparison cells of deferent sheets. i thing from the data validation this canot happen

It should do with Named Ranges, attach an example workbook

attach an example workbook!

See attached.

Enter any value in any of the bordered boxes in Column C. If the value exists in Sheet2, column D, you will get a message...

To extend the validation down column C... just copy any cell with validation.. select the range you want to copy to and go to Edit|Paste Special.. select Validation.