How to resolve two conflicting codes and still make them work

KK12345

New Member
Joined
Feb 19, 2013
Messages
28
Hi,
In sheet i have one code "change" to transfer data to next sheet when a condition is met.

Code:
Private Sub Worksheet_[COLOR=#ff0000]Change[/COLOR](ByVal Target As Range)
Sheets("Closed Flts").Unprotect "abcde"
  If Target.Column = 16 And Target.Cells.Count = 1 Then
    Target.EntireRow.Copy Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Target.EntireRow.Delete Shift:=xlUp
    Sheets("Closed Flts").Protect "abcde"
    End If
End Sub

Same sheet I have another "change" code to initiate emails via activation of code in module 12.

Code:
[COLOR=#333333][I]Private Sub Worksheet_[/I][/COLOR][COLOR=#ff0000][I]Change[/I][/COLOR][COLOR=#333333][I](ByVal Target As Range) If Target.Cells.Count = 0 Then Exit Sub[/I][/COLOR]
[COLOR=#333333][I]If Not Application.Intersect(Range("column12"), Target) Is Nothing Then[/I][/COLOR]
[COLOR=#333333][I]If Target.Value = "Overdue" Then[/I][/COLOR]
[COLOR=#333333][I]Call Mail_small_Text_Outlook[/I][/COLOR]
[COLOR=#333333][I]End If[/I][/COLOR]
[COLOR=#333333][I]End If[/I][/COLOR]
[COLOR=#333333][I]End Sub[/I][/COLOR]

Both of them conflict because of change.
How can I modify second code to make it work.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try combining them

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("column12"), Target) Is Nothing Then
    If Target.Value = "Overdue" Then
        Call Mail_small_Text_Outlook
    End If
Else
    Sheets("Closed Flts").Unprotect "abcde"
    If Target.Column = 16 And Target.Cells.Count = 1 Then
        Target.EntireRow.Copy Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete Shift:=xlUp
    End If
    Sheets("Closed Flts").Protect "abcde"
End If
End Sub
 

KK12345

New Member
Joined
Feb 19, 2013
Messages
28
Hi ,
Thanks but it does not work. It gives error ....Method Range of object worksheet failed.
The code debug states following
Private Sub Worksheet_Change(ByVal Target As Range)If Not Application.Intersect(Range("column12"), Target) Is Nothing Then
If Target.Value = "Overdue" Then
Call Mail_small_Text_Outlook
End If
Else
Sheets("Closed Flts").Unprotect "abcde"
If Target.Column = 16 And Target.Cells.Count = 1 Then
Target.EntireRow.Copy Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Target.EntireRow.Delete Shift:=xlUp
End If
Sheets("Closed Flts").Protect "abcde"
End If
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Then perhaps it should be

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 12 Then
    If Target.Value = "Overdue" Then
        Call Mail_small_Text_Outlook
    End If
Else
    Sheets("Closed Flts").Unprotect "abcde"
    If Target.Column = 16 And Target.Cells.Count = 1 Then
        Target.EntireRow.Copy Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete Shift:=xlUp
    End If
    Sheets("Closed Flts").Protect "abcde"
End If
End Sub
 

KK12345

New Member
Joined
Feb 19, 2013
Messages
28

ADVERTISEMENT

Thanks, It worked for second half of change and not the first one.
Now the email action still does not take place.
I have pasted the above code in sheet1 and the code for email is in module12.
How do I get this change overdue to trigger email code which in module12.
Thanks
 

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883

ADVERTISEMENT

<edited out="" due="" to="" drunken="" stupidity="">edit out due to stupidity

Carry on.</edited>
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
It works for me. To test it I used

Code:
Sub Mail_small_Text_Outlook()
MsgBox "Fired"
End Sub

in a regular module.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top