Combining VBA Codes

[jh3268]

Need some help combining 2 different VBA codes into my Excel sheet please.

Below are the separate codes for each and below that is what I have done to try to combine them and the error I am receiving. The top code is working but the bottom one is not. Not sure that it matters or not, but for the 2nd code, in order for column L to show "TRUE", it has to meet the following formula that I have in place: =IF(AND(TEXT(NOW()-B2,"[h]")*1>25,I2=""),TRUE,"")


Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column k after row 1 and is set to "YES"
If Target.Column = 11 And Target.Row > 1 And Target.Value = "YES" Then
Application.EnableEvents = False
' Copy columns A to L to complete sheet in next available row
Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Copy Sheets("COMPLETED").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub

------------------


Private Sub Worksheet_Changes(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column L after row 1 and is set to "TRUE"
If Target.Column = 12 And Target.Row > 1 And Target.Value = "TRUE" Then
Application.EnableEvents = False
' Copy columns A to L to complete sheet in next available row
Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Copy Sheets("SLA EXCEEDED").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub
And here is currently what I have when I tried to combine the 2. I am getting a runtime error 424: Object required.

 

[Joe4]

It looks like your formula is returning the boolean value of TRUE (no quotes) and not a text value of "TRUE".

So try removing the quotes from around the value of TRUE in your code, i.e.

If Target.Column = 12 And Target.Row > 1 And Target.Value = TRUE Then

 

[Fluff]

Change these lines

Application.EnableEvents = True
End If
If Target.Column = 12 And Target.Row > 1 And Target.Value = "TRUE" Then

to

Application.EnableEvents = True
ElseIf Target.Column = 12 And Target.Row > 1 And Target.Value = "TRUE" Then

 

[jh3268]

It looks like your formula is returning the boolean value of TRUE (no quotes) and not a text value of "TRUE".

So try removing the quotes from around the value of TRUE in your code, i.e.

If Target.Column = 12 And Target.Row > 1 And Target.Value = TRUE Then

Change these lines

Application.EnableEvents = True
End If
If Target.Column = 12 And Target.Row > 1 And Target.Value = "TRUE" Then

to

Application.EnableEvents = True
ElseIf Target.Column = 12 And Target.Row > 1 And Target.Value = "TRUE" Then

No error, but the 2nd code is still not working. Tried the "TRUE" with both quotes and without quotes as previously suggested as well.

 

[Fluff]

Missed this bit

but for the 2nd code, in order for column L to show "TRUE", it has to meet the following formula that I have in place:

A worksheet change does not get triggered by a formula.

 

[Joe4]

A worksheet change does not get triggered by a formula.

Ah Monday morning! I looked right past that detail!
I was focusing too much on the error...

 

[jh3268]

Thank you both, I didn't realize this. Appreciate the help!

 

[Joe4]

You are welcome.

The only formula to fire when changes in calculated values happen is the "Worksheet_Calculate" event.
However, that has one big caveat -- "Worksheet_Calculate" cannot tell which cell's value changed, only that some cell somewhere on the sheet was re-calculated.
That is one nice thing about "Worksheet_Change" or "Worksheet_SelectionChange" is that they can tell you exactly which cell triggered the code to run (the "Target" cell).
But they only work with manual selections/changes.