Regex: match and highlight string

[Nelson78]

Hello everybody.

I've to match a string - cellz - in a range of cells.

The task is ok like this:

Dim cellz As Variant
    For Each cellz In sheet1.Range("A2:A" & lr_col)
                With CreateObject("VBScript.RegExp")
                    .Global = True
                    .IgnoreCase = True
                    .Pattern = "\b" & cellz & "\b"
                For Each r In sheet2.Range("AG2:AG" & lr_ris)
                    If .test(r.Value) Then r.Interior.Color = vbRed
                Next r
        End With
    Next cellz

As you can see, I'm able of highlighting the cells where the string is found:

 If .test(r.Value) Then r.Interior.Color = vbRed

But I need something more: I need to write in yellow characters the string when found.

I mean: imagine cellz = "Paris"

Imagine cell where I'm looking for = "I don't live in Paris but I live in London"

I need like this: "I don't live in Paris but I live in London"

How can I perform the task?

 

[Norie]

Try this.

Dim r As Range
Dim cellz As Range
Dim regEx As Object
Dim fnd As Object
Dim itm As Object

    Set regEx = CreateObject("VBScript.RegExp")
    
    For Each cellz In Sheet1.Range("A2:A" & lr_col)

        With regEx
            .Global = True
            .IgnoreCase = True
            .Pattern = "\b" & cellz & "\b"

            For Each r In Sheet2.Range("AG2:AG" & lr_ris)

                Set fnd = .Execute(r.Value)

                For Each itm In fnd
                    r.Characters(itm.firstindex + 1, itm.Length).Font.ColorIndex = 6
                Next itm

            Next r

        End With
    Next cellz

 

[Nelson78]

Try this.

Dim r As Range
Dim cellz As Range
Dim regEx As Object
Dim fnd As Object
Dim itm As Object

    Set regEx = CreateObject("VBScript.RegExp")
 
    For Each cellz In Sheet1.Range("A2:A" & lr_col)

        With regEx
            .Global = True
            .IgnoreCase = True
            .Pattern = "\b" & cellz & "\b"

            For Each r In Sheet2.Range("AG2:AG" & lr_ris)

                Set fnd = .Execute(r.Value)

                For Each itm In fnd
                    r.Characters(itm.firstindex + 1, itm.Length).Font.ColorIndex = 6
                Next itm

            Next r

        End With
    Next cellz

Yes, it seems to work well.
And, in the same time, in order of painting the cell in red, is this ok? Or you suggest something different?

                For Each itm In fnd
                    r.Characters(itm.firstindex + 1, itm.Length).Font.ColorIndex = 6
                    r.Interior.Color = vbRed
               Next itm

 

[Norie]

I'm not sure I follow 100%.

Do you want to format the cell interior as red and the font of the found words as yellow?

 

[Nelson78]

I'm not sure I follow 100%.

Do you want to format the cell interior as red and the font of the found words as yellow?

Exactly.

 

[Norie]

What you added should work then as if no matches are found then the loop will never be entered.

 

[Nelson78]

What you added should work then as if no matches are found then the loop will never be entered.

Something is getting wrong in the following snippet:

Dim cellw As Variant, r4 As Range, fnd4 As Object, itm4 As Object

    For Each cellw In ref.Range("B2:B" & lr_ref)
        If cellw <> "" And cellw <> "*CODE27*"  Then
                   
                    With CreateObject("VBScript.RegExp")
                        .Global = True
                        .IgnoreCase = True
                        .Pattern = "\b" & cellw & "\b"
                            For Each r4 In result.Range("AG2:AG" & lr_ris)
                                If r4.Interior.Color <> vbRed Then
                                    Set fnd4 = .Execute(r4.Value)
                                        For Each itm4 In fnd4
                                            r4.Characters(itm4.FirstIndex + 1, itm4.Length).Font.ColorIndex = 7
                                            r4.Interior.Color = vbYellow
                                        Next itm4
                                End If
                            Next r4
                    End With
        End If
    Next cellw

At the end of the process, In the analysed range I can find a lot of times in different rows, inside a text of about 150 characters:

CODE27

with ColorIndex = 7 and interior.color = vbYellow

Even stranger, there aren't cellw at all containing CODE27 in any position.

What could it be happened?

 

[Norie]

Can you post some sample data?

 

[Nelson78]

Can you post some sample data?

I got it!

One of cellw is:

C.D.2.


So, I think the "." is something like a jolly.

How could I fix this?

 

[Norie]

If you want search for the literal character '.' you'll need to escape it.

To do that you would need to replace '.' with '\.' in the pattern.