Find and highlight a specific word in a range of cells

excel, vba

Solution

Let's say your excel file looks like htis

To color specific word, you have to use the cell's `.Characters` property. You need to find where does the word start from and then color it.

Try this

Option Explicit

Sub Sample()
    Dim sPos As Long, sLen As Long
    Dim aCell As Range
    Dim ws As Worksheet
    Dim rng As Range
    Dim findMe As String

    Set ws = ThisWorkbook.Sheets("MYSHEET")

    Set rng = ws.Range("G3:G1362")

    findMe = "Background"

    With rng
        Set aCell = .Find(What:=findMe, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            sPos = InStr(1, aCell.Value, findMe)
            sLen = Len(findMe)

            aCell.Characters(Start:=sPos, Length:=sLen).Font.Color = RGB(255, 0, 0)
        End If
    End With
End Sub

OUTPUT

Problem

I want to find a specific word in a range of cells then highlight it in red. To do so I created this code but it just worked on one line and highlighted all the cell text: ``` Sub Find_highlight() Dim ws As Worksheet Dim match As Range Dim findMe As String Set ws = ThisWorkbook.Sheets("MYSHEET") findMe = "Background" Set match = ws.Range("G3:G1362").Find(findMe) match.Font.Color = RGB(255, 0, 0) End Sub ```

Original source