VBA/Macro to replace or swap values in a list

[SendHalp3]

Hi all!

Example: I have a list of names that I want other users to place a numerical value next to in the form of 1 through 5. I did set up a data validation list so they can only choose between a pre-set numbers.

What I'd like to happen is say they want to rank NameA as 2 - which NameB is already two - so I want to have something automatically replace NameB's rank with 1 (NameA's former rank) and leave NameA with the new user-set rank of 2. Essentially swapping the two to avoid duplicate ranks

Currently it doubles up like this:

When I want it to automatically (no buttons or pop ups saying "hey theres a duplicate") change to look like this if a user changes the rank of a name and it matches another rank in the list:

I've tried looking this up and either I don't know how to word it so google understands what I'm looking for, or I'm out of my mind LOL. I'm self-taught with this, any help would be greatly appreciated!

 

[Marc L]

Hi, a VBA event demonstration to paste to the worksheet module :​

Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rg As Range, V
        Set Rg = [C4:C8]
    If Not Intersect(Target(1), Rg) Is Nothing Then
            V = Target(1).Value2
        With Application
            If .CountIf(Rg, V) > 1 Then
               .EnableEvents = False
               .ScreenUpdating = False
               .Undo
                Rg(.Match(V, Rg, 0)).Value2 = Target(1).Value2
                Target(1).Value2 = V
               .EnableEvents = True
               .ScreenUpdating = True
            End If
        End With
    End If
        Set Rg = Nothing
End Sub

 

[SendHalp3]

Hi! This is such a huge help, thank you! I have a follow up question after testing - it looks like when I change the rank from 2 to 1, it does the switch but say if i want to do 3 to 4, it reverts the one i changed back to 3. Is there a way to have it go both ways?

Thank you again!!

 

[Marc L]

As it well works on my side so link your workbook on a files host website like Dropbox​

and detail all your manual actions in order anyone can try to reproduce such behavior …​