Excel 2003 Deleting a row and moving it to another sheet

I know this question has been asked several times before, but since I am not as familiar with Excel as the users here I haven't been able to get any of the solutions provided to work for me. In Column I there is a Drop Down List with the values Open, Closed, and Completed. When I switch that to Completed I would like that row to be copied, moved to the Completed Sheet, and then removed from the Current sheet. Afterwards I would like to have all the rows moved up by 1 to avoid any blank rows. The solutions I have tried previously all encountered an error "Run-time error '13': Type Mismatch". When I click on Debug it shows that it is conflicting with my State color-coding script. The color-coding script works fine by itself and never encounters any issues. I'm not sure if this is something that will need to be changed or if I maybe messed something up when entering the code. Anyone able to help me figure this out?

I have attached a sample version of my spreadsheet so you can get a better idea of what is going on.

can you attach a sample file with almost 10 rows compiled ?

What happens if you switch wrongly to Completed ?
I think it's better to add a button for moving all the completed lines

I would be fine with a button to move the completed lines as well. Just as long as it can still move the remaining rows up to prevent any gaps.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I don't know, attach the file again

the line

Please Login or Register  to view this content.

must be

Please Login or Register  to view this content.

why did you change ?

you have also to modify the macro Private Sub Worksheet_Change(ByVal Target As Range) on sheet Current

Please Login or Register  to view this content.

and remove it from others sheets.