Hi Jan,

Am Thu, 7 Feb 2019 02:34:38 -0800 (PST) schrieb :

I have in principle two colums A and B. In column A I have continuous numbers, from 1 to 500. In Column B I havce names. Something like

1 Brian
2 James
3 Maria
4 Victoria
5 Henning
6 Rita
.
.
.

ans so on. What I like is to be able to type a new number for one of the names, and have the rest of the numbers change automatically in accordance, like:

If I type 2 next to Rita, the number next to James should change to 3, maria 4, and so on.

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2:C501")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Dim rngC As Range
Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each rngC In Range("A2:A" & LRow)
If rngC = Target And rngC < Target.Offset(, -2) Then
rngC = rngC + 1
Next
Target.Offset(, -2) = Target
End Sub


Regards
Claus B.
--
Windows10
Office 2016