Although Bob's is much better than mine when looking for xx, in this xxx was
hilited instead of just xx.
aaaxxaaaxaaaxxxaaa


--
Don Guillett
SalesAid Software

"Bob Phillips" wrote in message
...
Sub Highlight_Word()
Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim Mylen As Long

On Error GoTo endit
myword = InputBox("Enter the search string ")
If myword = "" Then Exit Sub
Mylen = Len(myword)
Set rng = ActiveSheet.UsedRange
rng.Cells.Font.ColorIndex = 0
For Each cell In rng
start_str = 0
Do
start_str = InStr(start_str + 1, cell.Value, myword)
If start_str Then
cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Loop Until start_str = 0
Next cell
endit:
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Cheng" wrote in message
ups.com...
Dear all,

I am trying to highlight all the same strings in one Excel cell.

For example, if the following text in one Excel cell,

Wish you all a wonderful wonderful weekend!

Using Gord Dibben's macro posted on Sep 19 2006 in this group (Please
see the following for details), only the first wonderful will be
highlighted.

Could someone share your expertise to let me know how to highlight the
second wonderful, i.e., all the same strings in one cell? Many thanks!
....Cheng

------------------------------------
Option Compare Text
Sub Highlight_Word()
Dim rng As Range
Dim cell As Range
Dim start_str As Integer
On Error GoTo endit
myword = InputBox("Enter the search string ")
If myword = "" Then Exit Sub
Mylen = Len(myword)
Set rng = ActiveSheet.UsedRange
rng.Cells.Font.ColorIndex = 0
For Each cell In rng
start_str = InStr(cell.Value, myword)
If start_str Then
cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Next cell
endit:
End Sub