Color font before and after delimiter

[RIchavalues]

Hi Experts,

Am new to VBA and would like to know if we can color text in a cell based on delimiter (-).

Say I have text "Welcome to - MrExcel" in cell "D22" then I want font color to be like this "Welcome to - MrExcel".

i.e., "Welcome to -" including hypen (-) in one color and "MrExcel" i.e., after hypen(-) in one color.

I tried to search in forum and also google but no help. Really appreciate any help provided.

Thanks,
Richa

 

[Gary McMaster]

Hi Richa

Welcome to the board.

Try the following with the hyphenated text in cell D22

Public Sub Test()

Dim oCell As Range
Dim iHyphenPos As Integer

Set oCell = ActiveSheet.Range("D22")

iHyphenPos = (InStr(1, oCell.Text, "-")) 'Character position of hyphen

'May need tests in addition to "hyphen exists" at next line

If iHyphenPos <> 0 Then ' If not zero, hyphen was found
    
    oCell.Characters(1, iHyphenPos - 1).Font.Color = RGB(0, 0, 255) 'Blue
    oCell.Characters(iHyphenPos + 1, (Len(oCell.Text)) - iHyphenPos).Font.Color = RGB(255, 0, 0) 'Red

End If


End Sub

 

[RIchavalues]

Thanks Gary. It worked.