Help on transferring data based on Condition in Cell

[USFengBULLS]

Hello all,
I am trying to have data in columns A,B & C of a single row be transferred to another sheet in same column A,B & C based on a drop down list in the row. So over in row I I have it labeled Status and the whole Column (Starting at I11:I500) is an in cell drop down list that has 3 options: Sent for approval, Approved and Rejected. If the User comes in and selects approved for that Row I want it to take the data in that same row in Column A, Column B and Column C and simply transfer it to another sheet (which is just a collection of all approved samples) in the same Columns A,B and C starting at Row A11, B11, and C11. I will also need this code to count up to find the next blank row for each time something's status is changed to approved. I think this has something to do with events at the worksheet level but I am not familiar with this as I normally write code for command buttons and similar objects. Can anyone please help me with is and also writing the code for the transfer as well? Thanks

 

[USFengBULLS]

Okay so I have written the code for the transfer:
Public Sub Status()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


For i = 11 To LastRow
If Cells(i, 9).Value = "APPROVED" Then
Range(Cells(i, 1), Cells(i, 3)).Select
Selection.Copy
Sheets("FINISH SCHEDULE").Select
erow = Sheets("FINISH SCHEDULE").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheets("FINISH SCHEDULE").Cells(erow, 1).Select
Sheets("FINISH SCHEDULE").Paste
End If
Next i


End Sub

My question still remains as to how to have this macro run every time a user clicks in the column Status for the in cell drop down and selects APPROVED?

 

[USFengBULLS]

Actually I got the Excel Event to do it from the drop down with this Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I11")) Is Nothing Then
Select Case Range("I11")
Case "APPROVED": APPROVED
End Select
End If
End Sub


but I need this to work for Cells in the Range I11:I500
If I type this in
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I11:I500")) Is Nothing Then
Select Case Range("I11:500")
Case "APPROVED": APPROVED
End Select
End If
End Sub

It does not work