Change color of certain characters in a cell

Question

I have the sentence "I would like 50 of those, please" in cell A1. I want to make any numeric characters red text (just the numeric characters). How do I do this? Here's the frame of what I have...

Sub RedText()

Dim i As Integer

For i = 1 To Len(Cells(1, 1).Value)
    If IsNumeric(Mid(Cells(1, 1).Value, i, 1)) = True Then
        'make the character red text
    End If
Next

End Sub

Any help would be greatly appreciated.

Answer 1

You can use a RegExp for the same effect.

The advantage of the Regex approach being the code will isolate immediately any groups of numeric characters (or skip any strings that have no numerics) rather than testing each individual character.

So it offers a speed advantage if you are processing reasonably large data sets

Sub RegExpRed()

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

    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "\d+"
        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

Answer 2

You can use the characters(start, length) property to do this. You can also store the text in a string and loop on that, which will be faster when you work with many cells. Here is an example:

Sub RedText()

Dim i As Long
Dim text As String

text = Cells(1, 1).Value

For i = 1 To Len(text)
    If IsNumeric(Mid$(text, i, 1)) = True Then
        Cells(1, 1).Characters(i, 1).Font.Color = vbRed
    End If
Next

End Sub