Excel VBA OnChange Event Duplicate Rows

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

edited Nov 22 '18 at 12:49

Pᴇʜ

21k42650

asked Nov 22 '18 at 11:31

Monika Rai

3071114

It's always helpful to mention 1) error text and 2) line with error.

– JohnyL
Nov 22 '18 at 11:46

You incorrectly copied text from your previous question: it must be For Each rw In Target.Rows.

– JohnyL
Nov 22 '18 at 11:47

add a comment |

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

edited Nov 22 '18 at 12:49

Pᴇʜ

21k42650

asked Nov 22 '18 at 11:31

Monika Rai

3071114

It's always helpful to mention 1) error text and 2) line with error.

– JohnyL
Nov 22 '18 at 11:46

You incorrectly copied text from your previous question: it must be For Each rw In Target.Rows.

– JohnyL
Nov 22 '18 at 11:47

add a comment |

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

edited Nov 22 '18 at 12:49

Pᴇʜ

21k42650

asked Nov 22 '18 at 11:31

Monika Rai

3071114

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

excel vba excel-vba

edited Nov 22 '18 at 12:49

Pᴇʜ

21k42650

asked Nov 22 '18 at 11:31

Monika Rai

3071114

edited Nov 22 '18 at 12:49

Pᴇʜ

21k42650

asked Nov 22 '18 at 11:31

Monika Rai

3071114

edited Nov 22 '18 at 12:49

Pᴇʜ

21k42650

edited Nov 22 '18 at 12:49

Pᴇʜ

21k42650

edited Nov 22 '18 at 12:49

Pᴇʜ

21k42650

asked Nov 22 '18 at 11:31

Monika Rai

3071114

asked Nov 22 '18 at 11:31

Monika Rai

3071114

asked Nov 22 '18 at 11:31

Monika Rai

3071114

It's always helpful to mention 1) error text and 2) line with error.

– JohnyL
Nov 22 '18 at 11:46

You incorrectly copied text from your previous question: it must be For Each rw In Target.Rows.

– JohnyL
Nov 22 '18 at 11:47

add a comment |

It's always helpful to mention 1) error text and 2) line with error.

– JohnyL
Nov 22 '18 at 11:46

You incorrectly copied text from your previous question: it must be For Each rw In Target.Rows.

– JohnyL
Nov 22 '18 at 11:47

It's always helpful to mention 1) error text and 2) line with error.

– JohnyL
Nov 22 '18 at 11:46

You incorrectly copied text from your previous question: it must be For Each rw In Target.Rows.

– JohnyL
Nov 22 '18 at 11:47

add a comment |

1 Answer
1

active

oldest

votes

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 '18 at 11:42

Takudzwa

1615

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53430029%2fexcel-vba-onchange-event-duplicate-rows%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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 '18 at 11:42

Takudzwa

1615

add a comment |

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 '18 at 11:42

Takudzwa

1615

add a comment |

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 '18 at 11:42

Takudzwa

1615

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 '18 at 11:42

Takudzwa

1615

answered Nov 22 '18 at 11:42

Takudzwa

1615

answered Nov 22 '18 at 11:42

Takudzwa

1615

answered Nov 22 '18 at 11:42

Takudzwa

1615

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk