Excel VBA OnChange Event Duplicate Rows

Question

I'm trying to make an event where I'm matching data from column N of two sheets and if data matches it will do the process of background fill of rows.

the code is as below. while using this I'm getting an error and can't able to put it on loop.

below is the code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set wkbDest = ThisWorkbook
Set wksDest_All = wkbDest.Worksheets("All Leads")
Set wksDest_New = wkbDest.Worksheets("New Leads")
    If Not Intersect(Target, Columns.Range("A:AS")) Is Nothing Then
        If Application.WorksheetFunction.CountA(Target) = 0 Then
             ' Not Empty
            For Each rw In Target.Row
                    If VBA.Trim(wksDest_All.Range("N" & rw).Value) = VBA.Trim(wksDest_New.Range("N" & rw).Value) Then
                    Target.Parent.Range("A" & rw.Row & ":AS" & rw.Row).Interior.ColorIndex = 15
                    Target.Parent.Range("A" & rw.Row & ":AS" & rw.Row).Borders.LineStyle = xlContinuous

                End If
            Next rw
          'Empty
            For Each rw In Target.Rows
                If VBA.Trim(wksDest_All.Range("N" & rw).Value) <> VBA.Trim(wksDest_New.Range("N" & rw).Value) Then
                    Target.Parent.Range("A" & rw.Row & ":AS" & rw.Row).Interior.ColorIndex = 0
                    Target.Parent.Range("A" & rw.Row & ":AS" & rw.Row).Borders.LineStyle = xlContinuous

                End If
            Next rw
          End If
    End If
End Sub

It's always helpful to mention 1) error text and 2) line with error. — JohnyL
– JohnyL, Commented Nov 22, 2018 at 11:46
You incorrectly copied text from your previous question: it must be For Each rw In Target.Rows. — JohnyL
– JohnyL, Commented Nov 22, 2018 at 11:47

Takudzwa · Accepted Answer · 2018-11-22 11:42:13Z

1

It would have been helpful if you gave information about the error itself.

I think your mistake is at the 8th line of your code:

For Each rw In Target.Row

That should have been

For Each rw In Target.Rows

answered Nov 22, 2018 at 11:42

Takudzwa

1733 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA OnChange Event Duplicate Rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related