Highlight the word overtime in cell

[FryGirl]

I have a workbook with many comments which I have used some code from contextures.com to pull all the comments into a seperate sheet.

What I would like to do is highlight all the instances of overtime.

<TABLE style="WIDTH: 85pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=113><COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 85pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=113>special overtime</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Overtime taken</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Double overtime</TD></TR></TBODY></TABLE>

Here is what I am trying, but can't figure out how to highlight overtime when it is in a cell with other words.

Sub FindWord()
    Dim cell As Range
    Dim r As Range
    For Each cell In Selection
        Set r = cell.Find(what:=" overtime ", Lookin:=xlValues, lookat:=xlWhole)
        If Not r Is Nothing Then
            With r
                .Interior.ColorIndex = 6
            End With
        End If
    Next cell
End Sub

 

[dave3009]

Perhaps use wildcards?

Sub FindWord()
    Dim cell As Range
    Dim r As Range
    For Each cell In Selection
        Set r = cell.Find(what:="*overtime*", LookIn:=xlValues, lookat:=xlWhole)
        If Not r Is Nothing Then
            With r
                .Interior.ColorIndex = 6
            End With
        End If
    Next cell
End Sub

 

[Jonmo1]

Try

Set r = cell.Find(what:="overtime", Lookin:=xlValues, lookat:=xlPart)

 

[FryGirl]

Thanks to both for the responses

Is there anyway to only highlight the word overtime? These suggestions hightlight the entire cell.

 

[Jonmo1]

Try

Sub FindWord()
    Dim cell As Range
    Dim r As Long
    For Each cell In Selection
        r = InStr(1, cell.Value, "overtime", vbTextCompare)
        If r > 0 Then
            With cell
                .Characters(r, Len("overtime")).Font.ColorIndex = 6
            End With
        End If
    Next cell
End Sub

This will not work (and cannot be done) if the cell values are Formulas

 

[FryGirl]

Thanks Jonmo works great. I can see now I was even barking up the wrong tree with .Interior.ColorIndex. Much appreciated.