How can I use Target.Address with offset-CodePudding

The purpose of my code is to copy from columns in the same row as the cell 'G4' when cell contents are changed. However, nothing is happening when I change G4's contents.

    Private Sub Worksheet_Change3(ByVal Target As Range)
    
        If Target.Address = "$G$4" Then
            Range("G4").Select
            Selection.Offset(0, -6).Resize(Selection.Rows.Count   0, _
            Selection.Columns.Count   2).Copy
        End If 
     End Sub

CodePudding user response：

To copy columns A:C on a row when the value in ColG is changed you could try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
   If Target.CountLarge > 1 Then Exit Sub
   
   If Target.Column = 7 Then Target.EntireRow.Range("A1:C1").Copy
   
End Sub

CodePudding user response：

A Worksheet Change: Copy Columns in the Same Row

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' Reference the source cell.
    Dim sCell As Range: Set sCell = Range("G4")
    
    ' 'Target' could be multiple cells e.g. if you paste a range. To check
    ' if one of those cells is the source cell and to attempt to reference it,
    ' use 'Intersect':
    Dim iCell As Range: Set iCell = Intersect(sCell, Target)
    
    ' Use the following line to check if 'iCell' is a range (or 'Nothing'):
    If Not iCell Is Nothing Then
        ' Reference columns 'A:C' in the row of the source cell
        ' by using 'EntireRow' with 'Columns':
        Dim irg As Range: Set irg = iCell.EntireRow.Columns("A:C")
        ' Copy the range.
        irg.Copy
    ' Else 'iCell' is 'Nothing' i.e. 'sCell' is not a cell of 'Target'
    End If

End Sub