VBA to Highlight Rows that Contain Any Keywords in Lookup Table

[seaottr]

Hi all,

I've scoured the internet for a solution and have tried coming up with a solution myself, but I'm just not proficient enough with VBA to be able to do it (and all of the solutions that I could find online involved a popup input box to manually enter keywords each time).

I'm trying to get a macro to look for any and all keywords in a table named "KeywordLookupTable" on another worksheet in a column called "Name" in the current active table and highlight the cell that contains it (or the entire row that contains it). The keywords should not be case sensitive and the list will be dynamic in the sense that I will change, add and delete keywords to be able to use in different data sets.

I don't want to use conditional formatting, as I would need to constantly recreate the rules for other files/data sets, and the file size would be a lot bigger as some of the data sets I use are huge.

I have sample data and keywords in the link to the spreadsheet below...if anyone is able to help me figure this out, I would be extremely appreciative!

Loading Google Sheets

Thank you in advance!

 

[seaottr]

Table Name: KeywordLookupTable

Lookup Word

agost

Bauer

LUKAS

GeOrGe

Data Set (current active table)

Sport	Nationality	Name	Age	Wt kg	Ht
Alpine Skiing	Pakistan	ABBAS Muhammad	23​	55​	168​
Figure Skating	United States	ABBOTT Jeremy	24​		175​
Freestyle Skiing	Ukraine	ABLAEV Enver	30​	68​	169​
Alpine Skiing	Georgia	ABRAMASHVILI Iason	21​	82​	176​
Ice Hockey	Canada	AGOSTA Meghan	23​	66​	168​
Speed Skating	South Korea	AHN Jee-Min	17​	52​	163​
Figure Skating	United States	AGOSTO Benjamin	28​		178​
Cross-Country Skiing	Czech Republic	BAUER Lukas	32​	73​	181​
Snowboard	Austria	GRUENER Lukas	28​	80​	177​
Short Track	China	SONG Weilong	20​	67​	179​
Biathlon	Poland	SZCZUREK Lukasz	21​	62​	178​
Freestyle Skiing	France	GEORGES Chloe	29​	57​	164​
Cross-Country Skiing	Sweden	LARSSON Mats	29​	80​	183​
Ice Hockey	United States	RAFALSKI Brian	36​	88​	178​
Cross-Country Skiing	Canada	GREY George	30​	76​	179​

Output (Cell Color should be highlighted instead of the text)

Sport	Nationality	Name	Age	Wt kg	Ht
Alpine Skiing	Pakistan	ABBAS Muhammad	23​	55​	168​
Figure Skating	United States	ABBOTT Jeremy	24​		175​
Freestyle Skiing	Ukraine	ABLAEV Enver	30​	68​	169​
Alpine Skiing	Georgia	ABRAMASHVILI Iason	21​	82​	176​
Ice Hockey	Canada	AGOSTA Meghan	23​	66​	168​
Speed Skating	South Korea	AHN Jee-Min	17​	52​	163​
Figure Skating	United States	AGOSTO Benjamin	28​		178​
Cross-Country Skiing	Czech Republic	BAUER Lukas	32​	73​	181​
Snowboard	Austria	GRUENER Lukas	28​	80​	177​
Short Track	China	SONG Weilong	20​	67​	179​
Biathlon	Poland	SZCZUREK Lukasz	21​	62​	178​
Freestyle Skiing	France	GEORGES Chloe	29​	57​	164​
Cross-Country Skiing	Sweden	LARSSON Mats	29​	80​	183​
Ice Hockey	United States	RAFALSKI Brian	36​	88​	178​
Cross-Country Skiing	Canada	GREY George	30​	76​	179​