conditional formatting and lookup table

Hi Everyone, I am having trouble making some VB code automatically update. I have cribbed the code but have to manually alter the reference numbers....which rather defeats the object. The spreadsheet is for a pick list in a small warehouse. Any help would be marvelous and much appreciated. Thanks