Highlight Text

[Welsh Mark3]

I have a column of notes - lets say this is column A. I want to highlight just part of the the text. In this case it just "#addingvalue"

I can use the Conditional formatting function but to my knowledge it highlights the entire cell, I just want to highlight of change color of text just for the phase shown above.

 

[Welsh Mark3]

The range will remain the same, I am potentially looking for multiple strings from the same cells and to highlight them differently. For example #addingvalue = Red
Health check = Green
Additional = Blue

The specific colors are not important more so that they are different by string

 

[Fluff]

Also do you want all the strings the same colour?

 

[Fluff]

Ok, try

Sub WelshMark3()
   Dim Cl As Range
   Dim x As Long, i As Long
   Dim Ary As Variant
   
   Ary = Array("#addingvalue", "[COLOR=#0000ff]and[/COLOR]", "[COLOR=#0000ff]east[/COLOR]", "[COLOR=#0000ff]town north[/COLOR]")
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      For i = 0 To UBound(Ary)
         x = InStr(1, Cl.Value, Ary(i), vbTextCompare)
         If x > 0 Then Cl.Characters(x, Len(Ary(i))).Font.ColorIndex = Choose(i + [COLOR=#ff0000]1, 3, 4, 5, 46[/COLOR])
      Next i
   Next Cl
End Sub

Change values in blue to match your strings & add more if needed.
Change the values in red to match the colour index you want.
List of colourindex numbers can be found here http://dmcritchie.mvps.org/excel/colors.htm

 

[Welsh Mark3]

Ok, try

Sub WelshMark3()
   Dim Cl As Range
   Dim x As Long, i As Long
   Dim Ary As Variant
   
   Ary = Array("#addingvalue", "[COLOR=#0000ff]and[/COLOR]", "[COLOR=#0000ff]east[/COLOR]", "[COLOR=#0000ff]town north[/COLOR]")
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      For i = 0 To UBound(Ary)
         x = InStr(1, Cl.Value, Ary(i), vbTextCompare)
         If x > 0 Then Cl.Characters(x, Len(Ary(i))).Font.ColorIndex = Choose(i + [COLOR=#ff0000]1, 3, 4, 5, 46[/COLOR])
      Next i
   Next Cl
End Sub

Change values in blue to match your strings & add more if needed.
Change the values in red to match the colour index you want.
List of colourindex numbers can be found here http://dmcritchie.mvps.org/excel/colors.htm

Thank you very much this is extremely useful

 

[Fluff]

You're welcome & thanks for the feedback