Validate that entry in one cell matches an entry in another range of cells

Hi -

See attached workbook sample. I need two separate procedures:

Procedure #1:

I have a workbook designed to capture product information and which contains two worksheets. Sheet 1 will capture high-level information about the products while Sheet 2 will capture price information about the same products.

Users will begin by entering high-level data on Sheet1 including entering the Part Numbers in column A. Once Sheet 1 is complete users will then begin entering data on Sheet 2. Once again, they need to enter Part numbers on Sheet2 column A. I want to validate the part number(s) they enter on Sheet2 Column A match a Part number previously entered on Sheet1 Column A. If no match is found, I merely want to highlight the cell on Sheet 2 in yellow. For this procedure, there no need for any drop down list on Sheet 2 that forces the user to enter a valid part number.


Procedure #2:

This procedure is something I plan to use in an Add-In I'm developing. Here I do want to create a drop down list that forces user to enter a valid Part number on Sheet 2/ column A.

Thanks,

I have a solution to both your problems, not requiring a helper column.

Procedure 1 involves a conditional format with an expanding range to include any new data entered with a match function.

Procedure 2 involves a named range that is dynamic to include your part numbers on sheet 1 (as long as there aren't any gaps), and turns that range into a source for the data validation on sheet 2.

Procedures.xlsx

Although both solutions contained in this thread work, I need a solution that can be deployed via an add-in (i.e., a module containing code that performs these tasks). Because users that install the add-in may be working with workbook templates that already contain data, I need to run procedure #1... highlight part numbers on sheet 2 that do not match part numbers entered on sheet 1. Secondly and because other users will be starting with blank workbook templates, I would also like to include a drop down list on sheet2 so it is easy for the user to select part numbers that match what is contained on sheet 1. Hopefully this makes sense.

Thanks,

Grilleman