Color text content contained within brackets

Question

Got an excel spreadsheet being used for some text content (I know, I know), but I'm wondering if anyone would know how to highlight specific text inside of a cell contained within brackets.

Cell would contain "Text goes here [bracketed red text] and so on and so on" with the "[bracketed red text]" a red color. Any help would be appreicated!

Answer 1

You need to create a macro. In Excel 2010, press View, Macros, View Macros. Type the name "Dummy" and press Create. In the new window that appears, look at the Project pane on the left. Double-click the sheet you want to apply the formatting to. Copy and paste the following code.

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

If you want the formatting to only apply to certain cells, you can insert one of the following lines before the Dim Text As String line.

    If Target.Row <> 12 Then Exit Sub    ' Only format row "$12"
    If Target.Column <> 12 Then Exit Sub ' Only format column "$L"

Now, whenever you finish editing a cell on the worksheet, it will change the square brackets and the text inside to red. It also [works] if there are [multiple] sets of [square] brackets, but [nested [brackets] will] fail.