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
Bookmarks