VBA to Highlight Rows that Contain Any Keywords in Lookup Table

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 and if any of those words are contained in a column called "Name" in the current active table, it highlights 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, keywords and output (only a portion of the original dataset) in the attached spreadsheet...if anyone is able to help me figure this out, I would be extremely appreciative!

Thank you in advance!

According to your attachment a VBA demonstration as a beginner starter :

PHP Code: 

Sub Demo1()
            Dim W, Rf As Range, R&
    With Sheet1.ListObjects(1).DataBodyRange.Columns(3)
               .Interior.ColorIndex = xlNone
        For Each W In Sheet2.ListObjects(1).DataBodyRange.Value
               Set Rf = .Find(W, , xlValues, 2)
            If Not Rf Is Nothing Then
                       R = Rf.Row
                Do
                           Rf.Interior.ColorIndex = 37 + (Rf(0).Interior.ColorIndex = 37)
                       Set Rf = .FindNext(Rf)
                Loop Until Rf.Row = R
            End If
        Next
    End With
               Set Rf = Nothing
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Wild Cross Posting ‼

If I knew this I did not post anything … Whatever the Excel forum you must read forum rules !

Anyway I should have written « an Excel basics UKV layout VBA demonstration » …
But as a beginner starter you could / should easily mod it as per your will …

Thanks for the rep' !