Two variables for Target.Value
 

Below is my code that works for any items identified as "In Progress" what if
I want to follow the same process for both "In Progress" and "Not Started"?
how would I udate the code to include both.

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 7 And _
Target.Row 4 And _
Target.Value < "In Progress" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

--
Thank you for your time!
John

Two variables for Target.Value
 

Replace the If condition:

If Target.Column = 7 And _
Target.Row 4 And _
(Target.Value < "In Progress" Or Target.Value < "Not Started") Then


--
Regards!
Stefi



€žVery Basic User€� ezt Ã*rta:

Below is my code that works for any items identified as "In Progress" what if
I want to follow the same process for both "In Progress" and "Not Started"?
how would I udate the code to include both.

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 7 And _
Target.Row 4 And _
Target.Value < "In Progress" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

--
Thank you for your time!
John

Two variables for Target.Value
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range

with Target
If .Count 1 Then Exit Sub
If IsEmpty(.Value) Then Exit Sub
If .Column = 7 And _
.Row 4 And _
.Value < "In Progress" And _
.Value < "Not Started" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(.Row, 1), Cells(.Row, 10)).Copy Dest
.EntireRow.Delete
End If
End With
End Sub

Note This I used a with statement for Target. It makes the code a bit more
compact and also a shade faster.
--
HTH...

Jim Thomlinson


"Very Basic User" wrote:

Below is my code that works for any items identified as "In Progress" what if
I want to follow the same process for both "In Progress" and "Not Started"?
how would I udate the code to include both.

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 7 And _
Target.Row 4 And _
Target.Value < "In Progress" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

--
Thank you for your time!
John

Two variables for Target.Value
 

Thank you, I used the above solution, but this would have worked too. Thank
you very mucy!
--
Thank you for your time!
John


"Jim Thomlinson" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range

with Target
If .Count 1 Then Exit Sub
If IsEmpty(.Value) Then Exit Sub
If .Column = 7 And _
.Row 4 And _
.Value < "In Progress" And _
.Value < "Not Started" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(.Row, 1), Cells(.Row, 10)).Copy Dest
.EntireRow.Delete
End If
End With
End Sub

Note This I used a with statement for Target. It makes the code a bit more
compact and also a shade faster.
--
HTH...

Jim Thomlinson


"Very Basic User" wrote:

Below is my code that works for any items identified as "In Progress" what if
I want to follow the same process for both "In Progress" and "Not Started"?
how would I udate the code to include both.

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 7 And _
Target.Row 4 And _
Target.Value < "In Progress" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

--
Thank you for your time!
John

Two variables for Target.Value
 

Very helpful! Thank you very much!
--
Thank you for your time!
John


"Stefi" wrote:

Replace the If condition:

If Target.Column = 7 And _
Target.Row 4 And _
(Target.Value < "In Progress" Or Target.Value < "Not Started") Then


--
Regards!
Stefi



€žVery Basic User€� ezt Ã*rta:

Below is my code that works for any items identified as "In Progress" what if
I want to follow the same process for both "In Progress" and "Not Started"?
how would I udate the code to include both.

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 7 And _
Target.Row 4 And _
Target.Value < "In Progress" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

--
Thank you for your time!
John

Two variables for Target.Value
 

You are welcome! Thanks for the feedback!
--
Regards!
Stefi



€žVery Basic User€� ezt Ã*rta:

Very helpful! Thank you very much!
--
Thank you for your time!
John


"Stefi" wrote:

Replace the If condition:

If Target.Column = 7 And _
Target.Row 4 And _
(Target.Value < "In Progress" Or Target.Value < "Not Started") Then


--
Regards!
Stefi



€žVery Basic User€� ezt Ã*rta:

Below is my code that works for any items identified as "In Progress" what if
I want to follow the same process for both "In Progress" and "Not Started"?
how would I udate the code to include both.

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 7 And _
Target.Row 4 And _
Target.Value < "In Progress" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

--
Thank you for your time!
John