I have some code I would like to execute automatically any time the value in C1 is changed:
Sub common2()
Dim sh As Worksheet, lr As Long, rng As Range, Brng As Range, Crng As Range
Set sh = Sheets(6) 'Edit sheet name
If sh.Range("C1").Value = "" Then
sh.Range("I2:I6").ClearContents
Else
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A1:A" & lr)
For Each C In rng
Set Brng = sh.Range("B:B").Find(C.Value, LookIn:=xlValues)
Set Crng = sh.Range("C:C").Find(C.Value, LookIn:=xlValues)
If Not Brng Is Nothing And Not Crng Is Nothing Then
sh.Cells(Rows.Count, 9).End(xlUp)(2) = C.Value
End If
Set Brng = Nothing
Set Crng = Nothing
Next
End If
End Sub
I've tried adding
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("C1")) Is Nothing Then
Set sh = Sheets(6) 'Edit sheet name
If sh.Range("C1").Value = "" Then
sh.Range("I2:I6").ClearContents
Exit Sub
Else
Call Sheet4.common2
End If
End Sub
but no matter how many examples I've looked at I still seem to be missing something because it never runs automatically. What am I doing wrong here?
Sub common2()
Dim sh As Worksheet, lr As Long, rng As Range, Brng As Range, Crng As Range
Set sh = Sheets(6) 'Edit sheet name
If sh.Range("C1").Value = "" Then
sh.Range("I2:I6").ClearContents
Else
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A1:A" & lr)
For Each C In rng
Set Brng = sh.Range("B:B").Find(C.Value, LookIn:=xlValues)
Set Crng = sh.Range("C:C").Find(C.Value, LookIn:=xlValues)
If Not Brng Is Nothing And Not Crng Is Nothing Then
sh.Cells(Rows.Count, 9).End(xlUp)(2) = C.Value
End If
Set Brng = Nothing
Set Crng = Nothing
Next
End If
End Sub
I've tried adding
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("C1")) Is Nothing Then
Set sh = Sheets(6) 'Edit sheet name
If sh.Range("C1").Value = "" Then
sh.Range("I2:I6").ClearContents
Exit Sub
Else
Call Sheet4.common2
End If
End Sub
but no matter how many examples I've looked at I still seem to be missing something because it never runs automatically. What am I doing wrong here?