Highlight cells if they contain another cell value

[Matt_Straya]

Hi, I cant for the life of me figure out the formula to highlight a cell if it contains the value from another list I can get something to work but it is never right.
Attached in the sheet. I want the "Tenure" column on sheet "MINAW" to look at the list on Sheet2 and highlight the relevant cell in the "Tenure" column.

 

[AlanSidman]

Here is a VBA solution:

Option Explicit

Sub MattWinter()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim lr As Long, lr2 As Long
    Dim i As Long, j As Long
    Set s1 = Sheets("MINAW")
    Set s2 = Sheets("Sheet2")
    lr = s1.Range("C" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr
        For j = 2 To lr2
            If InStr(s1.Range("C" & i), s2.Range("A" & j)) > 0 Then
                s1.Range("C" & i).Interior.ColorIndex = 6
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "Review Completed"
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Fluff13]

Using CF, select C2:C1089 & use this formula
=SUM(--ISNUMBER(SEARCH(things,C2)))>0

 

[bosco_yip]

Please try this shorter CF formula, it will return the same result as per Fluff's suggested formula

=OR(--ISNUMBER(SEARCH(things,C2)))

Regards
Bosco

 

[Matt_Straya]

Excellent -thank you so much! Fluff13 formula works perfectly. bosco_yip formula highlights only 2 numbers half way down the list. It may be the way I entered it? The VBA also works great - thank you!!

 

[Fluff13]

You're welcome & thanks for the feedback