VBA: Bold Certain Text in Cell

[NeoSez]

Hi I was looking all over for a simple script to bold only certain text within cells. I tried to record a macro, but it keeps bolding all text within each cell.
I only want a phrase within the cell. For example: "blah blah blag and to "THE MOON".
I found a script but it has that dreaded popup box to enter the text each time.
Can someone help me? Thank you.

 

[JLGWhiz]

Assume cell A2 contains a string of several words including "the moon".

Sub t()
Dim ch As Long
ch = InStr(Range("A2"), "moon") - 4
If Mid(Range("A2").Value, ch, 3) = "the" Then
    Range("A2").Characters(ch, 8).Font.Bold = True
Else
    Range("A2").Characters(ch + 4, 4).Font.Bold = True
End If
End Sub

 

[NeoSez]

JLGWhiz Thank you for posting so quickly. If I add other text, it does not find the words. Can I modify it just bold the last line of the cell?​

Sorry about the bold text. It won't turn off.

 

[JLGWhiz]

The method I used would only find the first occurence of the word 'moon' and then use that as a marker to get a leading or trailing word. Offhand, I don't know of a method to find all occurences in a string and I am not inclined to do a lot of research on it. sorry.
Regards, JLG

 

[Peter_SSs]

If I add other text, it does not find the words. Can I modify it just bold the last line of the cell?

Please give us a more varied set of sample data (say 8-10 rows) with XL2BB and explain what results you want in relation to that sample data.