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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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!