Calling all gurus... tricky request.

Hi we are trying to maintain a schedule via Excel. Mainly the worksheet will house additional information in regards to the roll out, location, team required attendees, etc. However, the order can change at any given time and we wanted to use a vlookup with the information below.

At quick glance I thought Row-1 will be sufficient but they added a wrench in the end.

The question is, how can we keep this table sorted by QC #, and rearrange the “Order in Queue” automatically if we need to move a QC up or down in the queue? (i.e. if we change number 101 to order number 4 then 101,102,103,104,106 and 109 should be updated accordingly and bumped up by 1 in the queue order)... I hope this makes sense. Also, the launch date column should be static relative to the order in queue column—so those would need to update simultaneously.

Option Explicit
Private prevVal As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim minVal As Long
Dim maxVal As Long
Dim lastrow As Long
Dim inc As Long
Dim i As Long
On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Count = 1 Then
If Target.Column = 2 Then
If Target.Value < prevVal Then
minVal = Target.Value
maxVal = prevVal
inc = 1
Else
minVal = prevVal
maxVal = Target.Value
inc = -1
End If
lastrow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
If i <> Target.Row Then
If Me.Cells(i, "B").Value >= minVal And Me.Cells(i, "B").Value <= maxVal Then
Me.Cells(i, "B").Value = Me.Cells(i, "B").Value + inc
End If
End If
Next i
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Column = 2 Then prevVal = Target.Value
End If
End Sub