VBA code to scan worksheet for values and highlight matches

[rockstarlive]

The problem is conditional formatting only allows me to search for one value, while I need to lookup a range of values. This is the macro I've been using:

Sub ABC()

Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long

vntWords = Array("Adult", "Air", "Art", "Association", "Award", "Bar", "Bribe", "Campaign", "Candidate", "Cash", "Cash Advance", "Charitable", "Charities", "Charity", "City", "Civic", "Commission", "Community", "Compensation", "Conference", "Contribute", "Contribution", "Contributions", "Convenience", "Culture", "Customer")

With ActiveSheet.UsedRange
For lngIndex = LBound(vntWords) To UBound(vntWords)
Set rngFind = .Find(vntWords(lngIndex), LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
lngPos = 0
Do
lngPos = InStr(lngPos + 1, rngFind.Value, vntWords(lngIndex), vbTextCompare)
If lngPos > 0 Then
With rngFind.Characters(lngPos, Len(vntWords(lngIndex)))
.Font.Bold = True
.Font.Size = .Font.Size + 2
.Font.ColorIndex = 3
End With
End If
Loop While lngPos > 0
Set rngFind = .FindNext(rngFind)
Loop While rngFind.Address <> strFirstAddress
End If
Next
End With

End Sub

 

[GTO]

Greetings and welcome to the site

I am afraid that your question is unclear, at least to me.

Mark

 

[rockstarlive]

Greetings and welcome to the site

I am afraid that your question is unclear, at least to me.

Mark

Have a look at this formula:
=IF(SUMPRODUCT(COUNTIF(A9:AU9,"*"&Keywords!$A$5:$A$78&"*")),"POTENTIAL", "")

This gives a partial match but I need an exact match