Need help updating sort automatically, but only if a cell has a number

I am trying to automatically sort by date in my excel document. But only update if there is a number (0 or 1) in the Q column of the row I am making a change in.

Here is what I've got so far.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range(ActiveCell, "Q2:Q1000")) Is Nothing Then
 
Range("g1").Sort Key1:=Range("g2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
       
 End If
   
End Sub

---

When I run it I get Argument not optional. What I am expecting is, if there is nothing in the Q column of the row I am updating the date in (which is g column), then do nothing, but if there is a number (0 or 1), then resort the document.

Hi Ham,

Maybe something like:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)   
    If Not Intersect(Target, Range("G2:G1000")) Is Nothing Then
        If Range("Q" & Target.Row) = 0 Or Range("Q" & Target.Row) = 1 Then
            Range("G1:G1000").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        End If
    End If
End Sub

---

Hope this helps

Thanks for the help. I did make one change in red text below. Your version seemed to only update the G column and not all of the surrounding columns. I believe my change instead seems to sort all columns. But now it seems to sort regardless of whether there is a number in the corresponding q column?

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)   
    If Not Intersect(Target, Range("G2:G1000")) Is Nothing Then
        If Range("Q" & Target.Row) = 0 Or Range("Q" & Target.Row) = 1 Then
            Range("G1:G1000").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        End If
    End If
End Sub

---

[/QUOTE]

Maybe instead of:

Code:

---

If Range("Q" & Target.Row) = 0 Or Range("Q" & Target.Row) = 1 Then

---

Try

Code:

---

If Range("Q" & Target.Row) = "0" Or Range("Q" & Target.Row) = "1" Then

---

Otherwise maybe post your amended code.

Hope this helps

Thanks, that seems to work.

Follow up question, is there a way that after the sort I move the "active cell" to the cell that I just changed? So, for example, I change cell g14, and it auto sorts that row up to row 5, can I have the cursor, or "active cell" move up there too?

To do that we would need something to hold as a value to then later find, In the examples so far we do not have an identifier like UPC number or collegue ID.

Is there an identifier of some kind in another column?

Not there aren't any.

Next question i seemed to have saved a recorded macro for Ctrl+Shift+S, apparently when i click that combination i wind up with a horizontal and vertacal red lines. I've tried re-recording using the same buttons, but it doesn't seem to take. Any idea what the horizontal and vertical lines are, or how to reset a recorded macro?

What if I arbitrarily created a unique id for each line, then, how would I go about moving the cursor?

Looking back at previous examples the below should give you an idea, this is assuming the ID was in column F:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)   
    Dim tmpStr As String
   
    If Not Intersect(Target, Range("G2:G1000")) Is Nothing Then
        If Range("Q" & Target.Row) = "0" Or Range("Q" & Target.Row) = "1" Then
            tmpStr = Range("F" & Target.Row).Value
            Range("F1:G1000").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            Range("F:F").Find(tmpStr, , , xlWhole).Offset(, 1).Select
            tmpStr = vbNullString
        End If
    End If
End Sub

---

Hope this helps