How to find and replace part of a cell with formatted text

Question

In an Excel 2007 spreadsheet I want to find-replace with highlighting part of the text in a cell. Using find-replace reformats the entire cell though.

For example, if the cell contains:

Pellentesque vel massa sit amet magna eleifend placerat. Pellentesque dictum, nibh vitae tincidunt placerat, elit libero tristique tellus, vel imperdiet nulla tortor id diam. Mauris porta blandit vestibulum.

I want to find "Pellentesque" and replace it with Pellentesque.

Can this be done without VBE or formulas?

Answer 1

Maybe this would suit (be sure to select no more cells than necessary before running or this could take a while):

Sub FormatSelection()

Dim cl As Range
Dim SearchText As String
Dim StartPos As Integer
Dim EndPos As Integer
Dim TestPos As Integer
Dim TotalLen As Integer

On Error Resume Next
Application.DisplayAlerts = False
SearchText = Application.InputBox _
(Prompt:="Enter string.", Title:="Which string to format?", Type:=2)
On Error GoTo 0
Application.DisplayAlerts = True
If SearchText = "" Then
Exit Sub
Else
For Each cl In Selection
  TotalLen = Len(SearchText)
  StartPos = InStr(cl, SearchText)
  TestPos = 0
  Do While StartPos > TestPos
    With cl.Characters(StartPos, TotalLen).Font
      .FontStyle = "Bold"
      .ColorIndex = 3
    End With
    EndPos = StartPos + TotalLen
    TestPos = TestPos + EndPos
    StartPos = InStr(TestPos, cl, SearchText, vbTextCompare)
  Loop
Next cl
End If
End Sub

Should embolden and colour Red. Changes are not overwritten if macro is rerun. Comment out .ColorIndex = 3 if not to change colour.

(Based on @Skip Intro's pointer to SO15438731 question with amendment and some code from SO10455366 answer.)