Color partial text in cell when text is a result of formula (VBA)

[Schturman]

Hi to all
Is it possible to color a partial text in cell when text is a result of formula (not text written directly in cell)?
I know it not possible by Conditional formatting, but maybe by VBA code...
1. I want to colorize name and symbol "!" that comes after name (when symbol appear) to Red the color

2. I also prefer if possible, that text after symbol of Home () appears in Orange color, but also save the number 1 condition (when symbol "!" appear).
3. I want to colorize symbol Home () to a Green color

Is it possible to do with VBA ? Or at least one of this options ?

 

[Peter_SSs]

Is it possible to color a partial text in cell when text is a result of formula

I'm afraid not.

 

[Schturman]

Ok, it going to be long...
Afer few days of searching and combining of a few VBA cods, I got the wanted result (not completely all, but something..)
1. I got the idea of Meni Porat to convert the formula value to text value
2. I put my formulas to another cells and changed a little bit a code of Meni Porat that will convert formula values to text values where I need.
3. Also I added code for partial text coloring that will colorize the symbol "!" to Red color (in module section)
4. I added code to Sheet for run it automatically when changes appered in a specific ranges of cells
5. Now when I change something in a specific ranges, the Conditional formatting will update formula value and also code activated and change the text value + colorize the symbol "!"

Now a questions:
1. Is it heavy for Excel all this codes ? My file should include like 48 Sheets (12 months = 4 weeks/month). The codes will runs only when I do changes in the specific ranges.
2. Is it possible to update code for colorizing partial text that will colorize also the name before symbol "!" till comma that comes before the name ?
This is a code:
=====================
Sub paintText()
Set myRange = Range("E5:E35") 'The Range that contains the substring you want to change color
substr = "!" 'The text you want to change color
txtColor = 3 'The ColorIndex which repsents the color you want to change

For Each myString In myRange
lenstr = Len(myString)
lensubstr = Len(substr)
For i = 1 To lenstr
tempString = Mid(myString, i, lensubstr)
If tempString = substr Then
myString.Characters(Start:=i, Length:=lensubstr).Font.ColorIndex = txtColor
End If
Next i
Next myString

End Sub
=====================
Thanks