Data validation - Automatically updates values

[Bejbas]

Hello Everyone,

I found macro which allow to automatically update my old value.
But i have the similar problem like: user: jqfanco
https://www.mrexcel.com/forum/excel-questions/1036624-data-validation-updates.html

Anyone can help me with this issue?

I will be appreciate for your help

 

[James006]

Hello,

You could test the following modification:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
Dim Rng As Range
Dim last2 As Long


last2 = Worksheets("DropList").Cells(Application.Rows.Count, "F").End(xlUp).Row
For count_cells = 1 To Range("A1").CurrentRegion.Rows.Count - 3
    Set Rng = Worksheets("DropList").Range("F4: F" & last2 + 1)
    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

Hope this will help

 

[Bejbas]

Perfect!
Thanks very much.

 

[James006]

You are welcome ...