Dim oldValue As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Check if the selection is within columns A to Z and rows 1 to 100
If Target.Column < 1 Or Target.Column > 26 Or Target.Row > 100 Or Target.Cells.Count > 1 Then Exit Sub
' Store the old value of the cell
oldValue = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Long
Dim startRow As Long
Dim dataRange As Range
Dim matchString As String
Dim matchCell As Range
Dim currentColumn As Long
' Check if the change is within columns A to Z and rows 1 to 100
If Target.Column < 1 Or Target.Column > 26 Or Target.Row > 100 Then Exit Sub
' Set the range of data for the current column
startRow = 1 ' Starting row
lastRow = Cells(100, Target.Column).End(xlUp).Row ' Gets the last row with data in the current column
Set dataRange = Range(Cells(startRow, Target.Column), Cells(lastRow, Target.Column))
' Prevents this code from running during data clearing or formula insertion
If Target.Cells.Count > 1 Then Exit Sub
' Get the string being typed in the cell
matchString = Target.Value
' Check if the typed value matches any of the existing values in the data range
If Len(matchString) > 0 Then
For Each matchCell In dataRange
If LCase(Left(matchCell.Value, Len(matchString))) = LCase(matchString) Then
Application.EnableEvents = False
Target.Value = matchCell.Value ' Auto-fills the cell with a matching value
Target.SelStart = Len(matchString) ' Keeps the new text highlighted for user
Application.EnableEvents = True
Exit For
End If
Next matchCell
End If
End Sub
