Highlight Exact and Partial Text, 2 Sheets

Hello Excel Gurus,

I have given up trying to solve this problem for myself and have turned to you, for starts I'm working with Excel 2007.

Here's my basic idea of what I would like to accomplish... some of which I was able to complete. I would like a master workbook with a list of part numbers and a small description of a problem (work related). My other work book is a template that I use for all jobs. When I add a part number(s) to "BOM" sheet in the template worksheet, I would like all (if any) part numbers highlighted if they are present in the master workbook, even partial part numbers. It does not have to work backwards, meaning, the "BOM" sheet would be the only area where the highlights happen.

Currently this is were I'm at...

There is a total of 2 workbooks, and 3 sheets that are all tied to each other (other sheets in the template workbook do not matter). The "Part Problem List Master" is used for keeping all other workbooks, created from the template the same. The "Problem Part List Master" workbook is mapped into a sheet on the template workbook, and refreshes every time I open the template, having it on a separate sheet is important to me, since I do print the template for use on the shop floor.

I'm able to have exact matches highlight in light blue. I did this by taking the the "Problem Part" sheet in the template and making A:A into a defined name, called "Range2". Under the "BOM" sheet i used conditional formatting (custom rule) which you can see under manage rules.

=COUNTIF(Range2,B2)

I'm currently stuck and trying to make wildcards work and highlight partial text. Any thoughts?


Thanks,

Josh