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?

 

[Nelson78]

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.

This point is ok.

The complication is: I have a long and dynamic "cellw list" (currently 17000 records, next time could be very different in quantity and strings).
I should say, before executing the match: if cellw contains ".", then substitute with "\.".

Could this operation be performed?

 

[Norie]

You could try this.

.Pattern = "\b" & Replace(cellw.Value, ".","\.")  & "\b"

 

[Nelson78]

You could try this.

.Pattern = "\b" & Replace(cellw.Value, ".","\.")  & "\b"

Yes, it seems to work perfectly.

Thank's.