Change the color of certain characters in a cell

Question

I have the sentence "I would like 50 of them, please" in cell A1. I want the text with numeric characters to be red (only numeric characters). How can I do it? Here is a shot 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

For this you can use the property ( start, length ) . You can also save text in a line and in a loop that 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 

Answer 2

You can use RegExp for the same effect.

The advantage of the Regex approach, which is code, immediately isolates any group of numeric characters (or skips any lines that don't have a number), rather than checking every single character.

Thus, it offers an advantage in speed if you process 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