Type mismatch error, while deleting the cells

[Theprash]

I'm trying to write code like.. when user make change in worksheet code runs... so i choose "worksheet" and "change in dropdowns...
code deletes current row when user enters certain value in it, lets say when user types "complete" (actually its 'Dropdown' so, cell will be currently selected.) but when i try to delete that cells row it returns error... Error 13, type mismatch error...
my code is like this...


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "complete" Then Rows(ActiveCell.Row).EntireRow.Delete
End Sub

But It detects change... when i run it as..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "complete" Then MsgBox "Goal Completed"
End Sub

then it shows msg box..

so whats wrong while deleting rows...
Then further i want to shift this row (the one that is gonna deleted) to the end of the another sheet.... in the same file... but i'm stuck here....
I'm total newbee.. so, ill be happy if got the solution... also guidelines for next function...

 

[mumps]

Try the following macro. It assumes that you will be entering "complete" in column A. Change this to suit your needs. It copies the deleted row to Sheet2. Change this as well to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    On Error GoTo errhandler
    Application.EnableEvents = False
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub 'change Range("A:A") to suit your needs
    If Target = "complete" Then
        Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
errhandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub