VBA Target.Address for multiple cells

Joined
Jun 13, 2017
Messages
101
Hello, I have the below code that works perfectly if you enter/copy data one cell at a time.
But I copy/paste multiple cells all the time, at which point the code below gives me an error.

Is there a way to adapt the below code to work with multiple cells changing at the same time?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

        Dim x As Integer
        For x = 2 To Sheets("Delivery Schedule").Cells(Rows.Count, 3).End(xlUp).Row

        
        If Target.Column = 3 And Target.Row = x Then
            Dim z As Integer
            For z = 2 To Sheets("Obsolete Codes").Cells(Rows.Count, 1).End(xlUp).Row
                If Target.Value = Sheets("Obsolete Codes").Range("A" & z) Then
                    MsgBox "An obsolete code has been entered through the last action.", vbCritical
                End If
            Next z
            End If
        Next x

End Sub
<code></code>
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,058
Office Version
2019
Platform
Windows
See if this works
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long, c As Range, cFound As Range
    lRow = Sheets("Delivery Schedule").Cells(Rows.Count, 3).End(xlUp).Row

If Not Intersect(Target, Range("C2:C" & lRow)) Is Nothing Then
    For Each c In Intersect(Target, Range("C:C"))
        Set cFound = Sheets("Obsolete Codes").Range("A:A").Find(What:=c, LookIn:=xlValues, LookAt:=xlWhole)
         If Not cFound Is Nothing Then
            MsgBox "An obsolete code has been entered through the last action.", vbCritical
            Exit For
        End If
    Next
End If
End Sub
old code is meaningless without an explanation of the requirement.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,921
Messages
5,411,254
Members
403,353
Latest member
ecboy1605

This Week's Hot Topics

Top