Running proximity searches in Excel

It's possible to run proximity searches in Excel using the below Visual Basic code. It will highlight any cells in which two given terms appear within a number of words that you set. Simply copy the vba code into a new module in the project list:

. . . the macro will run and prompt you to enter the cell in which the search should begin, and then prompt you to enter the first term, the second term, and the highest number of words which should appear between these terms.

This macro will only search for the terms in the order in which you enter them. It will find when the second term is within X number of words after the first term, but not vice versa.

Thanks to will266 for posting this code here.

Sub proximitySearch()

Dim startCell: startCell = InputBox("Enter Starting cell for search; i.e. B2", "Starting Cell")

Dim termOne: termOne = InputBox("Enter first search term", "Search Term 1")

Dim termTwo: termTwo = InputBox("Enter second search term", "Search Term 2")

Dim proximity: proximity = InputBox("Enter maximum distance between terms", "Proximity Value")

Dim rng As Range, r As Range

Set rng = Range(startCell, Range("a" & Rows.Count).End(xlUp))

rng.Interior.ColorIndex = xlNone

With CreateObject("VBScript.RegExp")

.IgnoreCase = True

.Pattern = termOne + "\S*(\s\S+){0," + proximity + "} " + termTwo

For Each r In rng

If .test(r.Value) Then r.Interior.Color = vbRed

Next

End With

End Sub