If Value Exists on another worksheet, show error message

Hi.

I use an excel spreadsheet to track my stock, but i'm after a code to prevent me from having two records with the same barcode.

When adding new stock to the spreadsheet using a Worksheet called "New Stock", I type the barcode into cell D7 (along with other info about the stock in other cells on that workbook)

I then hit a macro which copies the information to a worksheet called "Stock List" (using vlookup to find the first empty row to paste it to)

I'm wondering if there is any way I can get excel to check whether the barcode I enter into D7/New Stock is already somewhere in column B/Stock List

Either by modifying the current macro which copies the info across, or by conditional formatting where say cell D8 turns red and tells me i'm an idiot.

Anyone have any ideas?

Thank you very much for the help.

Thank you for the help both of you.

Nathansav, I couldn't get your code to work - I don't really have a clue what i'm doing, but thank you for trying with me.

AlphaFrog, ideally I wanted to avoid using the CF idea because it isn't idiot proof, and as previously mentioned, I am an idiot.

However, thanks to your help I managed to bodge something together.

I used your code

Please Login or Register  to view this content.

And threw that in cell A40, well out of the way.

Then on my macro I did this:

Please Login or Register  to view this content.

Really apreciate the help, thank you.

You're bloomin' good you.

Thank you