How to change color of one word of Text in a cell

[Fincafresh]

Sir,

I have a paragraph in one cell and have to change color of the Text " Ben " to red color, wherever the word Ben showing. Only change the color of word Ben.

I tried using conditional formattting " specific text" and find and replace function, but entire cell highlighted with red color.

Excel sheet attached

Support will be highly appreciated.



Thanks in advance

[Pete_UK]

You won't be able to use Conditional Formatting to do that, as it works on the contents of the whole cell. You will need VBA to do what you want.

Ask a Moderator to move your thread into the VBA forum.

Hope this helps.

Pete

[Fincafresh]

Noted, thanks for your clarification.

[Marc L]

According to your attachment a VBA demonstration for starters :

Sub Demo1() 
  Const S = "Ben" 
    Dim L&, Rc As Range, P& 
        L = Len(S) 
    For Each Rc In ActiveSheet.UsedRange.SpecialCells(2, 2) 
        P = InStr(Rc, S) 
  While P 
        Rc.Characters(P, L).Font.Color = vbRed 
        P = InStr(P + 3, Rc, S) 
  Wend 
    Next 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄