worksheet change loop

[cblakley4]

I'm creating a spreadsheet to place data in a table based on the order that it is selected. I have a list of the available data and I am using data validation for each cell. I have code that will delete the last taken data from the list and set up the next cell with the updated list for data validation.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Application.ScreenUpdating = False
    Dim player As String
    Dim rwPos As Long
    Dim x As Long
    Dim LR As Long
 
    If Target.Address = "$B$2" Then
    player = Range("B2").Value
    LR = Range("$B$500").End(xlUp).Row
    For x = LR To 24 Step -1
    If Cells(x, "B") = player Then Cells(x, "B").EntireRow.Delete
    Next x
    With Range("C2")
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=NameData"
        End With
    End With
 
    Application.ScreenUpdating = True
    End If
End Sub

I want to put some kind of loop in so that if C2 were changed it will run this code,

If Target.Address = "$C$2" Then
    player = Range("D2").Value
    LR = Range("$B$500").End(xlUp).Row
    For x = LR To 24 Step -1
    If Cells(x, "B") = player Then Cells(x, "B").EntireRow.Delete
    Next x
    With Range("E2:E2")
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=NameData"
        End With
    End With
 
    Application.ScreenUpdating = True
 
    End If

And again if D2 were changed and so on through the range (B2:K24)

I have very little experience with programming, so any help would be appreciated.

 

[GlennUK]

Would this cause problems if people go back up the list and alter their choices? For example, if someone chooses Player1 in cell B2, and your code deletes that player from the choice list, BUT then they change their mind and then choose Player2 from cell B2, your code would also delete that player from the list. So you'd have 2 players deleted but only one of them has now been chosen.

You'd be better off designing a table that keeps tabs on what has been selected and adjusts a choice table accordingly ... this means that changes of choice will be reflected automatically.