Macro to find parts of words

  • Thread starter Thread starter lisa
  • Start date Start date
L

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
 
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
 
Back
Top