Cut row to new sheet based on dropdown list condition

[iwb257]

Greetings,

I am working on a tracker that has a dropdown list with nine selections as a project progresses through an approval system. I have already written a macro that adds the date the project moves to the next phase of the system in the cell adjacent to the column with the dropdown list.

Once the project is complete, and I select "Complete" in the drop down list, I would like that row of information to copy into a sheet titled "Closed," and also be deleted from the source sheet. Subsequent "complete" rows should be copied into the next empty row as to keep a running total of projects going.

Within the first macro I wrote, when "Complete" is selected, a completion date appears five cells to the right. I am not sure if this macro is causing issues with my second macro, of if it is the drop down list that is giving me issues.

I have tried to use some lines of vba from this forum already, but have not been able to get it to work.

The dropdown list is in column H.

The following is the code I have been attempting to use.

Private Sub Worksheet_Change(ByVal Target As Range) 
 
Dim LR As Long 
 
If Target.Count > 1 Then 
Exit Sub
 
If Target.Column = 8 And LCase(Target.Value) = "4.0     Completed" Then
    Application.EnableEvents = False
    With Sheets("Closed")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Target.EntireRow.Copy Destination:=.Range("A" & LR + 1)
        Target.EntireRow.Delete
        Application.CutCopyMode = False
        Application.EnableEvents = True
    End With
End If
End Sub

Any help with this challenge will be very appreciated.

In addition, if it is due to the drop down list that I have having a problem, I am not against using a button to do a search for the key phrase. However, since projects are only completed maybe one or two a week, I would rather have it occur automatically.

Thanks.

 

[VoG]

Try

If Target.Column = 8 And LCase(Target.Value) = "4.0     completed" Then