List of keywords to search from another sheet cell contains many other letters

Hi - I am looking to create a macro that would highlight the entire row of data if the cell contains the word.
Ideally, looking to work with 2 sheets, one sheet ("Sheet1") contains the keywords and the other sheet ("Data") has the data.

Sheet with data ("Data") may have many other words in the cell with the key word. Example in Sheet1=Keyword= "John Doe" and in the Data sheet ("Data") cell A6 contains "John Doe is a great man", A8 contains a man who is great is John Doe, or great is John Doe a man. the key word is absolute but want to find all instances by highlighting the row.

How do I modify the below

I have the following so far...

Public Sub HighlightListedValues()
Dim strConcatList As String
Dim cell As range

'Creates a string concatenating your list of strings, separated by |s
'e.g. "item1|item2|item3|item4|"
For Each cell In Sheets("Sheet1").range("A1:A8")
strConcatList = strConcatList & cell.Value & "|"
Next cell

'For each used cell in Column A of sheet1, check whether the value in that cell
'is contained within the concatenated string
For Each cell In Intersect(Sheets("Data").range("A:E"), Sheets("Data").UsedRange)
If InStr(strConcatList, cell.Value) > 0 Then 'InStr returns 0 if the string isn't found
cell.EntireRow.Interior.Color = RGB(255, 255, 0) 'Highlights the row in red if value found
End If
Next cell
End Sub

Submit an example workbook, with data, which shows what you want to accomplish.

As stnkynts said, an example with before and after makes life so much easier.

Run the code from the sheet with the keywords (Sheet1)

Please Login or Register  to view this content.

FWIW:

Please Login or Register  to view this content.

I have attached the a sample, if you run the macro it highlights the keyword in "Sheet1" in the "Data" tab. I am trying to make the macro capture all instances of the keyword when in a sentence and not just in an individual cell..As there are instances when the key word will be in a sentence in a cell. Example Row 5 should be highlighted because the keyword Mark is in the sentence "John Mark is Love"

Thank you again ever so much for all the great leverage and help - definitely giving attributes to this site in my dissertation.

Thank you Jindon - Absolutely gorgeous! it works just fine.

Kindest regards,

Ian

You are welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Task done