coloring specific text within a cell

[Tody03]

In My spread sheet i have the words Brkfst & NRF in the same cell. I want to color the Brkfst in Green and the NRF in Red.
How can I do it in a macro?
Thanks

 

[mrhstn]

Hi, so long as the cells contain text and not a formula you could try something like this. This is set to work on Sheet6, column H but you can adjust to suit

Sub ColorPart()
Dim StartC As Integer
Dim LenC As Integer
Dim lstRow As Integer
Dim i As Integer


With Sheet6
lstRow = .Range("H" & Rows.Count).End(xlUp).Row


For i = 1 To lstRow
        StartC = InStr(1, .Cells(i, 8).Value, "Brkfst")
        If StartC <> 0 Then
            LenC = 6
            .Cells(i, 8).Characters(Start:=StartC, Length:=LenC).Font.Color = vbGreen
        End If
        StartC = InStr(1, .Cells(i, 8).Value, "NRF")
        If StartC <> 0 Then
            LenC = 3
            .Cells(i, 8).Characters(Start:=StartC, Length:=LenC).Font.Color = vbRed
        End If
Next i
End With
End Sub