Format and Color Text in Excel Cell with VBA

Formatting a substring of a string in an Excel Cell is actually quite a challenging task. There are two ways to do it – select part of the substring that you wish to format and change its color. Then make it Bold, Italic or Underline like this:

However, there is a second, better way – use VBA and check every value after the substring for eligibility. If the substring is eligible, then simply format it. In our case, we would like to format anything numeric after importance=”, tid=” and index=”.

Thus, we need to search for every single character until the found substring and check whether it is numeric. If this is the case, format the character in the specific color and type. This should be enough:

Public Sub FormatAndColor()
    Range("B1") = Range("A1")
    ChangeAfter "importance=""", Range("B1"), vbRed
    ChangeAfter "index=""", Range("B1"), vbGreen
    ChangeAfter "tid=""", Range("B1"), vbBlue    
End Sub

Sub ChangeAfter(lookFor As String, currentRange As Range, color As Long)
    Dim i As Long
    Dim lenLookFor As Long: lenLookFor = Len(lookFor)
    For i = 1 To Len(currentRange)
        With currentRange
            If .Characters(i, lenLookFor).Text = lookFor Then
                While IsNumeric(.Characters(i + lenLookFor, 1).Text)
                    With .Characters(i + lenLookFor, 1).Font
                    .Bold = True
                    .color = color
                    End With
                    i = i + 1
                Wend
            End If
        End With
    Next i
End Sub

Quite small, but it works!

Cheers!