Create macro to search Worksheet for list of strings and highlight the row

Question

Can someone help me create a macro that will search an Excel worksheet for a list of 30 strings (e.g., SV-32488r1, SV-33485r1) and highlight the Row when found?

• I am using Office 2010.
• I am not a Excel or VBA wiz so I have no idea where to start.
• The searches I have found only allow me to search for one string.

Thank you so much in advance.

Answer 1

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("List").Range("A1:A30")
        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("Sheet1").Range("A:A"), Sheets("Sheet1").UsedRange)
        If InStr(strConcatList, cell.Value) > 0 Then       'InStr returns 0 if the string isn't found
            cell.EntireRow.Interior.Color = RGB(255, 0, 0) 'Highlights the row in red if value found
        End If
    Next cell
End Sub

This will highlight the relevant rows in red if the following is true:

• The data matching the values you are searching for are is in column A of a worksheet named "Sheet1"
• Your list of strings is contained in cells A1:A30 of a worksheet named "List"

Amend the names of worksheets and ranges as needed e.g. If you want to search in Columns A to C of a worksheet named "Data" you would amend Sheets("Sheet1").Range("A:A") to Sheets("Data").Range("A:C")

Hope this helps!