excelvba

Select multiple rows, copy and paste

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

For some of the rows, there is text within square brackets I need to be Red.

Example: ‘Insert text here [Highlighted Text]’

I am 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 so I need to select all, copy all, and paste all.
When I do this, an error message will pop up.

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