How to resolve two conflicting codes and still make them work

[KK12345]

Hi,
In sheet i have one code "change" to transfer data to next sheet when a condition is met.

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.

[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.

 

[VoG]

Try combining them

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]

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]

Then perhaps it should be

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]

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

 

[VoG]

Are you typing Overdue into Column L?

 

[nuked]

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

Carry on.</edited>

 

[KK12345]

Hi ,
That is correct. 'L'.
Any Luck?

 

[VoG]

It works for me. To test it I used

Sub Mail_small_Text_Outlook()
MsgBox "Fired"
End Sub

in a regular module.

 

[VoG]

I see that you have cross posted this Two codes conflict with eachother. How to resolve them

Please read Excelguru Help Site - A message to forum cross posters