vba - 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 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