Reordering numbers

[lovablebug]

I have a list of projects that are currently ordered by priority (see portion of this list in picture). I need a formula that will allow me to change say, #18 to #1 and will automically change the rest of the numbers to their new priority. So the current #1 would be #2, #2 would become #3, #3 to #4 and so on.

 

[trunten]

Stick this in a worksheet change module

Dim old As Integer, new As Integer
Dim I As Long

Application.ScreenUpdating = False
new = Target.Value 
Application.Undo
old = Target.Value
Target.Value = new

For i = 1 To ActiveSheet.UsedRange.Rows.Count
    If new > old Then
        If Cells(i, Target.Column) <= new Then
            Cells(i, Target.Column) = Cells(i, Target.Column) - 1
        End If
    Else If old > new Then
        If Cells(i, Target.Column) >= new And Cells(i, Target.Column) < old Then
            Cells(i, Target.Column) = Cells(i, Target.Column) + 1
        End If
    End If
Next i
Application.ScreenUpdating = False