|VBA can not change the font color of a portion of text that is the result of a formula since VBA will see the contents of the cell as a formula and not perform a "text operation" on it.|
You could set the font color of the entire cell to be red via VBA, but not just certain characters.
A workaround is to place your formula in a hidden cell and then use a Worksheet_Change macro to copy the value that the formula produces into a visable cell and then set the text color of characters in that cell.
I'm not going to try and reproduce your formula, so let's use this example:
C14 contains My dog
C1 contains is
D1 contains happy
In B10 I'll place this formula and then Hide the row:
=Calc!C14 & IF(B1="A"," " & C1 & " " & D1, "")
When B1 contains A, B10 will display:
My dog is happy
Since I can't format the text in that cell because the cell really contains a formula, I'll use the following code to place the value returned by that formula into B11.
B11 will now contain the text My dog is happy
Whenever any change is made to the sheet, this code will "copy" the result of B10 into B11, determine the length of C14 (LEN("My dog" = 5), add 1 to it and use that value as the Start argument to set all characters from there to the end of the string to be red.
The final result in B11 will be:
My dog is happy
Private Sub Worksheet_Change(ByVal Target As Range)
Dim chrNum As Integer
'Copy B10 to B11
Range("B11") = Sheets(1).Range("B10").Value
'Get start number from C14
chrNum = Len(Range("C14")) + 1
'Set font color of characters in B11
Length:=Len(Range("B11"))).Font.ColorIndex = 3