Excel VBA OnChange Event Duplicate Rows
0
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ᴇʜ
23.4k62951
asked Nov 22 '18 at 11:31
Monika RaiMonika Rai
3571214
add a comment |
0
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ᴇʜ
23.4k62951
asked Nov 22 '18 at 11:31
Monika RaiMonika Rai
3571214
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 beFor Each rw In Target.Rows.
– JohnyL
Nov 22 '18 at 11:47
add a comment |
0
0
0
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ᴇʜ
23.4k62951
asked Nov 22 '18 at 11:31
Monika RaiMonika Rai
3571214
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
excel vba excel-vba
edited Nov 22 '18 at 12:49
Pᴇʜ
23.4k62951
asked Nov 22 '18 at 11:31
Monika RaiMonika Rai
3571214
edited Nov 22 '18 at 12:49
Pᴇʜ
23.4k62951
asked Nov 22 '18 at 11:31
Monika RaiMonika Rai
3571214
edited Nov 22 '18 at 12:49
Pᴇʜ
23.4k62951
edited Nov 22 '18 at 12:49
Pᴇʜ
23.4k62951
edited Nov 22 '18 at 12:49
Pᴇʜ
23.4k62951
23.4k62951
asked Nov 22 '18 at 11:31
Monika RaiMonika Rai
3571214
asked Nov 22 '18 at 11:31
Monika RaiMonika Rai
3571214
asked Nov 22 '18 at 11:31
Monika RaiMonika Rai
3571214
3571214
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 beFor 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 beFor 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
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
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
0
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
TakudzwaTakudzwa
3625
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
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
TakudzwaTakudzwa
3625
add a comment |
0
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
TakudzwaTakudzwa
3625
add a comment |
0
0
0
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
TakudzwaTakudzwa
3625
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
TakudzwaTakudzwa
3625
answered Nov 22 '18 at 11:42
TakudzwaTakudzwa
3625
answered Nov 22 '18 at 11:42
TakudzwaTakudzwa
3625
answered Nov 22 '18 at 11:42
TakudzwaTakudzwa
3625
3625
add a comment |
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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