Conditional Formatting based on a selected range of data

[Perodin]

In one worksheet (Inventory) I have Item Numbers in column B that already exist in a database.

In another worksheet (Catalog 1) I have Item numbers and descriptions in column B that I would like highlighted if they contain the Item Numbers from the worksheet (Inventory).

My goal would be to see at a glance if entries in worksheet (Catalog 1) already exist in the database or not so that I may simplify my data management. Note that there are many catalog workbooks and I can either apply the formatting one at a time or as a whole. This should be easy, but with worksheet (Catalog 1) containing extended data I have not been able to find a way to accomplish my task. My data appears as such…

Raw Format
Worksheet (Inventory)	Worksheet (Catalog 1)
1275	2458 Springs, front (black) (2) .............................................................. $2.00
1502	2458A Springs, front (white) (2) .............................................................. $4.00
3757	5857 Springs, front (white) (progressive rate) (2) .................................... $5.00
3757A	3757 Springs, rear (black) (2) .............................................................. $2.00
5857	3757T Springs, rear (blue) (2) ................................................................. $4.00
6529	3757A Springs, rear (green) (2) ............................................................... $5.00
6921	3757X Springs, rear (white) (2) ............................................................... $4.00
7903A	5858 Springs, rear (white) (progressive rate) (2) ..................................... $5.00
8302	4957 Springs, red (for Ultra Shocks only) (2.5 rate) (f/r) (2) ............... $2.50

<tbody>
</tbody>

Ideal Result
Worksheet (Inventory)	Worksheet (Catalog 1)
1275	2458 Springs, front (black) (2) .............................................................. $2.00
1502	2458A Springs, front (white) (2) .............................................................. $4.00
3757	5857 Springs, front (white) (progressive rate) (2) .................................... $5.00
3757A	3757 Springs, rear (black) (2) .............................................................. $2.00
5857	3757T Springs, rear (blue) (2) ................................................................. $4.00
6529	3757A Springs, rear (green) (2) ............................................................... $5.00
6921	3757X Springs, rear (white) (2) ............................................................... $4.00
7903A	5858 Springs, rear (white) (progressive rate) (2) ..................................... $5.00
8302	4957 Springs, red (for Ultra Shocks only) (2.5 rate) (f/r) (2) ............... $2.50

<tbody>
</tbody>

 

[ztodd]

What have you tried so far? Conditional formatting can do this for you- but you might want to insert a column and develop your formula in that new column until you get it working. Look up the help for these functions : VLOOKUP, LEFT, FIND, ISERR or ISERROR

You'll probably want to put the functions together in some way like this - ISERROR(VLOOKUP(LEFT(catalogentry,FIND(" ",catalogentry)-1), inventoryrange, ... ))

Try developing the solution yourself rather than using someone else's code or formula- it will help you out.

When figuring out a formula, work on it from the inside out- one piece at a time.