ExcelBanter - Format particular words in a string

On Mar 11, 8:37*am, Glen wrote:
I have been trying this morning to find help topics for this and am
having a bit of trouble getting this to work. *Maybe somebody can
provide me a quick way to do this.

I have cells that I am placing a macro created concatenated string
into. *The string has key words in it that I would like to Bold for
emphasis. *These key words are "Module" and "Lessons". *How do I
search the string and bold those two words throughout the string
without bolding the entire cell?

Thanks,

GTW

I found the following code ina different group from David Peterson. I
changed the keywords and modified the range and it worked
beautifully. Dave Peterson - you rock!

Newsgroups: microsoft.public.excel.misc
From: Dave Peterson
Date: Wed, 10 Sep 2003 16:52:16 -0500
Local: Wed, Sep 10 2003 5:52 pm
Subject: Search and Replace
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Not built into excel. You could use a macro, though:

Option Explicit
Sub testme01()

Dim myWords As Variant
Dim myCell As Range
Dim myRng As Range
Dim FirstAddress As String
Dim iCtr As Long
Dim letCtr As Long

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants,
xlTextValues))
On Error GoTo 0

myWords = Array("test", "bold", "hilight")

If myRng Is Nothing Then
MsgBox "No Text Cells found in Selection"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
With myRng
Set myCell = .Find(What:=myWords(iCtr), After:=.Cells(1),
_
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not myCell Is Nothing Then
FirstAddress = myCell.Address
Do
For letCtr = 1 To Len(myCell.Value)
If StrComp(Mid(myCell.Value, letCtr, _
Len(myWords(iCtr))), _
myWords(iCtr), vbTextCompare) = 0
Then
myCell.Characters(Start:=letCtr, _

Length:=Len(myWords(iCtr))) _
.Font.FontStyle =
"Bold"
End If
Next letCtr

Set myCell = .FindNext(myCell)

Loop While Not myCell Is Nothing _
And myCell.Address < FirstAddress
End If

End With
Next iCtr

End Sub

You can just put the words you need to highlight in this line:

myWords = Array("test", "bold", "hilight")

(one word is ok, too.)

If you're new to macros, you may want to read David