Regex to replace and change text colour in Excel

James_Jones · Apr 4, 2018

Hi, I have the following VBA code that replaces text using regular expression which works as expected but would like to also change the replaced text colour to red.

Code:

Dim regex As Object

  Dim r As Range, rC As Range
   

  Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))
   
  Set regex = CreateObject("VBScript.RegExp")
  regex.Pattern = "<ph(.+)>(.+?)</ph>"
   
  For Each rC In r
   
      If rC.Value <> "" Then rC.Value = regex.Replace(rC.Value, "<ph$1>$2</ph>")
      
  Next rC

Thanks

James_Jones · Apr 4, 2018

The code did not dipslay correctly when i saved the post, here it is again

Code:

Dim regex As Object
Dim r As Range, rC As Range

Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))

Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "<ph(.+)>(.+?)</ph>"

For Each rC In r

    If rC.Value <> "" Then rC.Value = regex.Replace(rC.Value, "<ph$1>$2</ph>")
    
Next rC

</ph$1></ph(.+)>

Regex to replace and change text colour in Excel

James_Jones

New Member

Excel Facts

James_Jones

New Member

Similar threads

Forum statistics

Share this page

Regex to replace and change text colour in Excel

James_Jones

New Member

Excel Facts

James_Jones

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock