Apply font color to all matches (regex) under the worksheet

[bnpdx]

Hello,

I'm trying to create VBA macro that would color all text matches (using regex search) using red font.

I found the below VBA code online.

The problem is, I need it to look for matches across the entire worksheet and not only in cell A1.

Can you help me extend the functionality to include the entire worksheet?

I'm sorry for not being that VBA-savvy ;(

Thank you in advance!!

Sub RegExpRed()

    Dim objRegex As Object
    Dim RegMC As Object
    Dim RegM As Object

    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "\w+:"
        If .test(Cells(1, 1).Value) Then
            Set RegMC = .Execute(Cells(1, 1).Value)
            For Each RegM In RegMC
                Cells(1, 1).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = vbRed
            Next
        End If
    End With

End Sub

 

[6StringJazzer]

Changes in red, but caveat emptor I have not tested this.

Sub RegExpRed()

    Dim objRegex As Object
    Dim RegMC As Object
    Dim RegM As Object
    Dim Cell As Range

    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "\w+:"
        For Each Cell In UsedRange
           If .test(Cell.Value) Then
               Set RegMC = .Execute(Cell.Value)
               For Each RegM In RegMC
                   Cell.Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = vbRed
               Next
           End If
        Next Cell
    End With

End Sub