Как самый простой вариант:
Option Explicit
Dim vValue
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target <> vValue Then
Dim lp&, s$
s = Replace(Target.Value, vValue, "")
lp = InStr(1, Target.Value, s, 1)
If lp > 0 Then
Target.Font.Color = vbBlack
Target.Characters(lp, Len(s)).Font.Color = vbBlue
Else
Target.Font.Color = vbBlue
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then vValue = Target
End Sub