How to format text between branches excel using a macro Excel 2010

Question

I found a little macro to change all the text between "[" and "]" red in excel. But i can't get it to work in Excel 2010, i get some errors.

Can someone help me debug this? At the end i wish that in my excel sheets everything that is between "[" and "]" changes in font to red and everything thats outside of these criteria is still black.

This is the script i found on this site:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Text As String
    Dim Index1 As Long
    Dim Index2 As Long
    Text = Target.Text
    Index2 = 1
    Do
        Index1 = InStr(Index2, Text, "[")
        If Index1 = 0 Then Exit Do
        Index2 = InStr(Index1, Text, "]")
        If Index2 = 0 Then Exit Do
        Target.Characters(Index1, Index2 - Index1 + 1).Font.Color = &HFF
    Loop
End Sub

Answer 1

Consider:

Sub ColorMeRed()
   Dim r As Range, v As String, L As Long
   Dim RedMe As Boolean, i As Long
   For Each r In ActiveSheet.UsedRange
      v = r.Text
      If InStr(v, "[") > 0 Then
         L = Len(v)
         RedMe = False
         For i = 1 To L
            If r.Characters(i, 1).Text = "[" Then
               RedMe = True
            ElseIf r.Characters(i, 1).Text = "]" Then
               RedMe = False
            Else
               If RedMe Then r.Characters(i, 1).Font.Color = vbRed
            End If
         Next i
      End If
   Next r
End Sub

For example:

Answer 2

Change the line

Index1 = InStr(Index2, Text, "[")

to

Index1 = InStr(Text, "[")

The first version is trying to start searching at the position of Index2. Since Index2 has not had a value assigned, it equals 0; but InStr considers the first character of the string to be character 1. The changed version just has InStr start at the beginning of the string.