Auto execute a macro to trigger when text is written in a cell

[mhickey]

Hi there,
My company is using a quote sheet in which you input quantity of an item, the stock code of that item a description (measurements) and the price all in different columns. It calculates the quantity and price of the items and gives you an extended price in another column, also giving a total at the bottom including taxes, etc.

This is all good, but there are a million stock codes and I'd like to streamline it by adding the description and the price automatically if the stock code is inserted into it's given cell. This is what I did:

Sub COMPANYPRODUCT()

Select Case Range("B19").Value

Case "XL104"

Range("C19").Value = "AN ITEM!"
Range("D19").Value = "$128"

Case "XL102"

etc.

I want to make it automatically add that price to the price column, and let the qty/price columns take care of the extending of that price.

Does anyone out there know how I can automatically set this up so that anyone can type any stock code into the stock code cells in that entire column and it will bring up the description and price of that item? I also need it to happen automatically everytime Excel is opened. I have every stock code written in VBE with description and price as shown above so I hope I'm on the right track, it certainly took a while!!!

Any questions, let me know but I'm stuck! Any help is verryy much appreciated!!!!