Using Regex to Physicaly Highlight Non-match with Font Color

[ExcelMonkey]

I have a function which uses Regex to stip non-numerics from a string. The
code below highlights I wrap a function around label1 caption and then pass
its equivalent with non-numerics stripped out to label2. Pretty straight
forward.

Instead of replacing the original string in label1 with a new revised string
in label2, I would like to restate the originaly string from label1 in label2
AND change the font colour of the non-matches in label2. That is if my
string was $A$300in label1, I want to use a different font colour to
highlight "$","A", and "$" from "300" in label2.

Is it possible to do this in VBA? Do I need to create a collection in my
function and then pass each character individually through the regex test?

'************************************************
Sub Thing()
Dim NewString As String

NewString = StripNonNumerics(UserForm1.Label1.Caption)
UserForm1.Label2.Caption = NewString

End Sub
***********************************************
Public Function StripNonNumerics(strA As Variant) As String
Dim objRegExp As Object
Dim objcolRegMatch As Object
Dim objRegMatch As Object

Set objRegExp = CreateObject("Vbscript.RegExp")
With objRegExp
.Pattern = "\d+"
End With

Set objcolRegMatch = objRegExp.Execute(strA)
For Each objRegMatch In objcolRegMatch
StripNonNumerics = StripNonNumerics & objRegMatch
Next

Set objRegExp = Nothing
Set objcolRegMatch = Nothing

End Function

Thanks

EM

 

[ExcelMonkey]

This seems to work. This example does not have a userform but simply has the
text string in cell D3 of the worksheet

Sub StripNonNumerics()

Dim regEx, Match, Matches

Set regEx = CreateObject("Vbscript.RegExp") ' Create a regular
expression.
regEx.Pattern = "\d+" ' Set pattern.
regEx.IgnoreCase = False ' Set case insensitivity.
regEx.Global = True ' Set global applicability.

Set Matches = regEx.Execute(Worksheets("Sheet1").Range("D3").Text) '
Execute search.

For Each Match In Matches ' Iterate Matches collection.
'selects a range from the index of the character to the index of the
character plus the length of the word
ActiveCell.Characters(Match.FirstIndex, Match.FirstIndex +
Len(Match.Value)).Font.FontStyle = "Bold"
Next

End Sub

 

[ExcelMonkey]

I spoke too fast. This does not always work. Will look into it.

EM

 

[ExcelMonkey]

This works:

Sub BoldNumerics()

Dim regEx, Match, Matches

Set regEx = CreateObject("Vbscript.RegExp") ' Create a regular
expression.
regEx.Pattern = "\d+" ' Set pattern.
regEx.IgnoreCase = False ' Set case insensitivity.
regEx.Global = True ' Set global applicability.

Set Matches = regEx.Execute(Worksheets("Sheet1").Range("D3").Text) '
Execute search.

For Each Match In Matches ' Iterate Matches collection.
'selects a range from the index of the character to the index of the
character plus the length of the word
ActiveCell.Characters(Match.FirstIndex + 1,
Len(Match.Value)).Font.FontStyle = "Bold"
Next

End Sub