I'm very new to VBA and would greatly appreciate any help that I can get on this.

I have an excel workbook with two worksheets. Vendors contains a list of vendors in column A and Products has Item Name in column F that contains strings with the vendor name and item description together.

I am trying to loop through the list of vendors (100 ) and search for that vendor name in the Item Name column, and highlight the row if there is a vendor match.

I have been looking through posts and I've been able to find and highlight a match for a static search value, but I haven't been able to find anything that will continue the search by looping through the vendor names.

Any insights you could provide would be greatly appreciated.

CodePudding user response：

Apologies if this isn't quite what you're looking for, but it might achieve a similar goal.

You can use array formulas to determine which vendors appear in the products table as well as which products contain vendors that are found in the vendors table. As you can see this does not depend on vendor or product names containing any fixed patterns.

Cell B3 contains the following:

=PRODUCT(--(SUBSTITUTE(Table2[Products],A3,"")=Table2[Products]))=0

Cell E3 contains the following:

=SUM(LEN(D3)-LEN(SUBSTITUTE(D3,Table1[Vendors],"")))>0

From here you can use conditional formatting to highlight the rows that evaluate to TRUE.