VBA Code to change color of partial text in cell that contains formula and text

[vlad7984]

Hi, I am interested in changing the color of partial text within a cell. I am able to do this with regular text in cells, but when there is a formula that concatenates 2 strings of text or text and if formula then code doesn't work.

The cell includes a text string and if statement.
"If losses are greater than $100,000,000" & if(B5>100,000,000,"(Yes)","(No")

The vba code i used to change partial color:
Sub HighlightStrings()
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
cFnd = InputBox("Enter the text string to highlight")
y = Len(cFnd)
For Each Rng In Selection
With Rng
m = UBound(Split(Rng.Value, cFnd))
If m > 0 Then
xTmp = ""
For x = 0 To m - 1
xTmp = xTmp & Split(Rng.Value, cFnd)(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
xTmp = xTmp & cFnd
Next
End If
End With
Next Rng
Application.ScreenUpdating = True
End Sub

Does anyone know how I can get this to work on a hybrid cell (text + formula) and ideally I'd like this to be done automatically without a prompt.

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
I'm afraid that you cannot change the font colour of part of a cell if it contains a formula.

 

[vlad7984]

Ah yes that is what I figured after hours of searching. This was a last resort. Thank you though!

 

[Fluff]

You're welcome