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

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

Thank you in advance!

 

[Fluff]

Duplicate to: VBA to Highlight Rows that Contain Any Keywords in Lookup Table

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.