Excel VBA: Automatically apply macro to cells

Question

I'm currently learning the automatically triggered macro for a cell. I am curious if this can apply to a range of cells instead of 1 by 1? My case is: If I input in any cell in column A, "Hello" will appear in column B in the corresponding row. My question is that what if, for instance, I input in A1 (then B1 will appear "Hello"), then I drag from A1 to A10, how can I make the macro automatically apply to B2 -> B10? Currently, I run into the "run time error '13' - Type mismatch".

My current script:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer    
i = Target.Row    
    If Not Intersect(Target, Range("A:A")) Is Nothing Then            
        If Target <> "" Then            
            Cells(i, 2) = "Hello"                
        Else            
            Cells(i, 2).ClearContents                
        End If        
    End If        
End Sub

SJR SJR · Accepted Answer · 2018-06-15T08:34:22

Use Offset, which is relative, and loop through Target if it is multiple cells.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer, r As Range

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    For Each r In Intersect(Target, Range("A:A")
        If r <> "" Then
            r.Offset(, 1).Value = "Hello"
        Else
            r.Offset(, 1).ClearContents
        End If
    Next r
End If

End Sub

Excel VBA: Automatically apply macro to cells

1 Answers