Data validation updates

[jqfranco]

I have an Excel file with two worksheets.


In the "ListSource" spreadsheet on lines A2; A150, I have the list that will feed the droplist.


In the "DropList" worksheet I have a droplist on the lines F3: F503.


When I change a value in my list in "ListSource", this change is reflected in the spreadsheet "DropList", in all cells that had the old value, which is correct.


The problem is that when I add a value in the list in "ListSource", in the "DropList" worksheet, in all droplist cells that are missing values, it adds the new value.


For example, if your I have cells written between the lines F3: F20, it puts the new value in the cells between F21 and F503, without having done anything at all.


The code is placed in the Worksheet ListSource Change

Private Sub Worksheet_Change (ByVal Target As Range)
Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
Dim Rng As Range
For count_cells = 1 To Range ("A1"). CurrentRegion.Rows.Count - 3
    Set rng = Worksheets ("DropList") Range ("F4: F503")
    If Intersect (Target, Range ("A" & count_cells + 3)) Is Nothing Then
    Else
        Application.EnableEvents = False
        new_value = Target.Value
        Application.Undo
        old_value = Target.Value
        Target.Value = new_value
        rng.Replace What: = old_value, Replacement: = new_value
        Target.Select
    End If
Next count_cells
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

If someone can help me solve this problem, thank you in advance.


Happy New Year 2018 for all of you