finding exact text.

[CRogers]

Hello,

I have a spreadsheet an old colleague created where there is a macro that looks for key words. What I'm trying to do is fix it so that only catches exact words. i.e. I'm looking for "airman" and it picks up "chairman". I have the list of key words. Question : is there a way that I can make it only highlight if it says "airman"?

Thanks in advance for any help!

 

[Fuji]

Based on your original code.

Sub test()
    Dim x, mycolor, i&, n&, r As Range, m As Object
    Application.ScreenUpdating = False
    Columns(1).Font.ColorIndex = xlAutomatic
    Columns(1).Font.Bold = False
    mycolor = Array(255, 16711680, 16746752, 16747007, 35072, 16771707, _
                48383, 48300, 8406527, 16762537)
    x = Filter([transpose(if(i1:i10000<>"",i1:i10000))], False, 0)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "([$()^|\\\[\]{}+*?-])"
        For i = 0 To UBound(x)
            x(i) = "(" & .Replace(x(i), "\$1") & ")"
        Next
        .Pattern = "\b(" & Join(x, "|") & ")\b"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            For Each m In .Execute(r)
                For i = 1 To m.submatches.Count - 1
                    If m.submatches(i) <> "" Then
                        With r.Characters(m.firstindex + 1, m.Length).Font
                            .Color = mycolor(i - 1)
                            .Bold = True
                        End With
                        Exit For
                    End If
                Next
            Next
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

Why don't you just use Conditional Formatting?

If your list of words in Column I and your words to search in in Column A

Conditional Formatting formula:
=OR(ISNUMBER(FIND(" " &$I$1:$I$1000&" "," "&A1&" ")))

Book2
	A	B	C	D	E	F	G	H	I
1	The chairman is here								airman
2	I saw the airman								pilot
3	The funny papers are cool								fun
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=OR(ISNUMBER(FIND(" " &$I$1:$I$1000&" "," "&A1&" ")))	text	NO

Scott,
That does not seem to work in all cases. Consider the situations in which the phrase begins with the word you are are looking for, or there is punctuation after the word you are looking for.
Here are some examples where it does not work properly.

Rows 1 and 3 should also be formatted red.

 

[Scott Huish]

Scott,
That does not seem to work in all cases. Consider the situations in which the phrase begins with the word you are are looking for, or there is punctuation after the word you are looking for.
Here are some examples where it does not work properly.

View attachment 113954

Rows 1 and 3 should also be formatted red.

Depends on the source data of course, starting word could be fixed by switching to Search instead of find, but not the punctuation

 

[Alex Blakenburg]

Love the regex suggestions, just have to wonder what will happen when the Scripting DLL that it relies on is deprecated.

VBScript to be deprecated

To my fellow VBA developers. If you have projects that depend on the Scripting Runtime library, beware. VBScript is going to be turned off and subsequently deprecated on Windows clients. Things like the Dictionary object, the FileSystemObject and other useful libraries will no longer be...

www.mrexcel.com