[SOLVED] Change colour of text in square brackets

Change colour of text in square brackets

I have data in a google sheet and would like to select all - copy - paste into excel.

For some of the rows, there are text within square brackets where I need it to be in Red.

Example: ‘Insert text here [Highlighted Text]’

I am currently using this macro:

    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

It works if I move data one row by one row.

However, it’s a few thousand rows of data so I will need to select all, copy all, and paste all. And when I do this, the macro does not work anymore (error message will pop up).

Im very new to excel macros so if possible please guide me step by step, I will really appreciate it!

Solution

When you paste a range, then the Target will be also a range not a single cell.
Therefore need to iterate through the range.
The below mod of your code will do that.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Text As String
Dim Index1 As Long
Dim Index2 As Long

For Each cel In Target
Text = cel.Text
Index2 = 1
    Index1 = InStr(Index2, Text, "[")
    If Index1 <> 0 Then
        Index2 = InStr(Index1, Text, "]")
        If Index2 <> 0 Then
            cel.Characters(Index1, Index2 - Index1 + 1).Font.Color = &HFF
        End If
    End If
Next cel
    End Sub