Worksheet_Change(ByVal Target As Range) throws error

acat · Aug 15th 2019

trying to have cell value change based on another cell value change

code below throws this error

[ATTACH=JSON]{"data-align":"none","data-size":"large","data-attachmentid":1221701}[/ATTACH]

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intersection As Range
    Set intersection = Intersect(Target, Worksheets("Sheet1").Range("G1"))
    If Not intersection Is Nothing Then
        If Worksheets("Sheet1").Range("G1").Value = Worksheets("Sheet20").Range("K2").Value Then
        Worksheets("Sheet1").Range("F2").Value = Worksheets("Sheet20").Range("I2").Value
        End If
    End If
End Sub

**alansidman** · Aug 15th 2019

Give this a try:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("G1") Then
        If Target.Value2 = Sheets("Sheet20").Range("K2") Then
            Sheets("Sheet1").Range("F2") = Sheets("Sheet20").Range("I2")
        End If
    End If
End Sub

acat · Aug 15th 2019

on line

Code

If Target.Value2 = Sheets("Sheet20").Range("K2") Then

[ATTACH=JSON]{"data-align":"none","data-size":"large","data-attachmentid":1221707}[/ATTACH]

**Kenneth Hobson** · Aug 16th 2019

Code

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range
  
  Set r = Intersect(Target, Range("G1"))
  If r Is Nothing Then Exit Sub
  
  Application.EnableEvents = False
  If r.Value = Sheets("Sheet20").Range("K2") Then _
    Range("F2") = Sheets("Sheet20").Range("I2")
  Application.EnableEvents = True
End Sub

Display More

Worksheet_Change(ByVal Target As Range) throws error

Participate now!

Share