Finding Keywords from Text String - Formula

[JackChang]

All-

I have a list of descriptions on tab one and a list of keywords on tab two. Is there a formula that looks up my keywords on tab two, against the list on tab one? I'm trying to find where they match.

For example, if I lookup my keyword 'BRP120AF' on tab one, it will match rows 1423, 1424, 1425 (CIR BRP120AF PLUG ON 1P20A AFC).

Either highlighting these rows, or having an 'X' in a blank column works.

Any ideas? Sample file attached.

Thanks in advance!

[Aussiebear]

I have removed the "Solved" signage as no solution has been provided so far

[snb]

CF:


=NOT(ISERR(SEARCH("BRP120AF";$A1)))

Applies to $A$1:$A$2000

[Paul_Hossler]

I did it so that double clicking on a tag on Sheet2 will color the corresponding Sheet1 values green



Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rLookup As Range, rSearch As Range, rFirstOne As Range
Dim i As Long, n As Long
Dim sLookup As String

Set rLookup = Target.Cells(1, 1)

If rLookup.Column <> 1 Then Exit Sub

sLookup = rLookup.Value
If Len(sLookup) = 0 Then Exit Sub

Set rSearch = Worksheets("Sheet1").Cells(1, 1).CurrentRegion.Columns(1)

With rSearch
.Interior.ColorIndex = xlColorIndexNone

n = 0
Set rFirstOne = Nothing
For i = 1 To .Rows.Count
If InStr(.Cells(i, 1).Value, sLookup) > 0 Then
.Cells(i, 1).Interior.Color = vbGreen
n = n + 1
If rFirstOne Is Nothing Then Set rFirstOne = .Cells(i, 1)
End If
Next i
End With

If Not rFirstOne Is Nothing Then
rFirstOne.Parent.Select
Application.Goto rFirstOne, True
End If

MsgBox n & " matches found for " & sLookup


End Sub