Macro to find parts of words

[lisa]

Can anyone tell me how to write macro that will find part
of a word in a column of data and highlights it.

For eg.

HelloBye
hi
HelloWhy
Bye


In this column, I want anything with string Hello in it to
be highlighted. So both HelloBye and HelloWhy will be
highlighted in this example.

Thanks in advance for your help.
Lisa

 

[pfsardella]

Tested using Excel 97SR2 on Windows 98SE. Please watch for linewrap.

I adapted the following routine from the VBE help on the FindNext
method. Assumption is that the range you're searching is on the Active
Sheet in Range("A1:A500"). If "Hello" is found, then the entire cell
background is highlighted in gray.

Sub FindMe()

Dim rngC As Range
Dim strToFind As String, FirstAddress As String

strToFind = "Hello"

With ActiveSheet.Range("A1:A500")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.Interior.Pattern = xlPatternGray50
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> _
FirstAddress
End If
End With

End Sub

HTH
Paul