Macro Help

[amwalo]

I am trying to make my code work on the entire column by clicking a button. I am trying to get it to work with out having to highlight and having the one click take car of the entire column if that makes sense. The code deletes an entire row with completed in the column and moves it to another sheet. Side question is there a way to have it work by just hitting enter after entering completed?

Here is my code:

'VBA Code by Scott from SpreadsheetPlanet.com
Sub move_rows_to_another_sheet()
For Each mycell In Selection.Columns(9).Cells
If mycell.Value = "Completed" Then
mycell.EntireRow.Copy Worksheets("Completed").Range("A" & Rows.Count).End(3)(2)
mycell.EntireRow.Delete
End If

Next

End Sub

 

[mumps]

I tried the macro on some dummy data and it worked properly. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Candyman8019]

I was able to reproduce your type-mismatch error. It occurred when more than once cell in column I was changed at the same time. Do you have any formulas in Column I that would change when anyone types "Completed"?

 

[amwalo]

I was able to reproduce your type-mismatch error. It occurred when more than once cell in column I was changed at the same time. Do you have any formulas in Column I that would change when anyone types "Completed"?

I have conditional formatting to turn it green.

 

[Candyman8019]

That should not be an issue... Try this:
change:
If target.Value = "Completed" Then

to

If target.text = "Completed" Then

 

[amwalo]

That should not be an issue... Try this:
change:
If target.Value = "Completed" Then

to

If target.text = "Completed" Then

Okay I will try it.