Data validation - Automatically updates values

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You could test the following modification:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,942
Members
409,848
Latest member
Blomsten
Top