Replace active cell with different value

[DogsbodyBoy]

I have what might be a rather unusual objective. I would like to know if there is VBA code that can be applied to a worksheet so that when a value is entered into a cell, such as the letter A for example, a lookup table containing two columns is searched. If the entered letter exists in the 1st column, then it is automatically replaced with the value in the second column.

For clarity, suppose the two columns in the lookup table are populated like this:
Column 1 Column 2
A NORTH
B SOUTH
C EAST
D WEST

So if the letter C is entered into a worksheet cell, it is automatically replaced with EAST.

Note. Column 1 is the single letters A, B etc. Column 2 is North, South, etc.

Thanks for any help.

 

[DanteAmor]

Change in the code "A2:D10" by the cell range where you want it to apply.

Change in the code "F2:F4" by the cell range where you are going to put columns 1 and 2, it is not necessary to put the range of column 2, putting the range of column 1 is enough.


Try:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A2:D20")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Dim f As Range
    
    Set f = Range("F2:F5").Find(Target.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      Application.EnableEvents = False
        Target.Value = f.Offset(, 1).Value
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[DogsbodyBoy]

Thank you DanteAmor. Your solution works perfectly.

I've tried to take my objective one step further by adding formatting to the cell after the lookup has occurred. However, it doesn't work as intended because when I enter the letter A into cell A1, your macro logic correctly pulls in the value from the 2nd column of the lookup table but my formatting applies to cell A2. In other words, hitting the enter key after entering a value into a cell moves the active cell down one position. Your logic is correctly applying before that down action occurs but mine is occurring afterwards. What do I have to do to make mine occur before?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1:C3")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Dim f As Range
    
    Set f = Range("F1:F4").Find(Target.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      Application.EnableEvents = False
        Target.Value = f.Offset(, 1).Value
      Application.EnableEvents = True
    End If
  End If
  
   With ActiveCell.Characters(Start:=2, Length:=1).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = True
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With

End Sub

 

[DanteAmor]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1:C3")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Dim f As Range
    
    Set f = Range("F1:F4").Find(Target.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      Application.EnableEvents = False
        Target.Value = f.Offset(, 1).Value
        With Target.Characters(Start:=2, Length:=1).Font
          .Name = "Calibri"
          .FontStyle = "Regular"
          .Size = 11
          .Strikethrough = False
          .Superscript = True
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .ThemeColor = xlThemeColorLight1
          .TintAndShade = 0
          .ThemeFont = xlThemeFontMinor
        End With
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[DogsbodyBoy]

Marvelous! Thank you so much, DanteAmor. I will now review the logic to understand the approach. Have a great day.

 

[smozgur]

Thank you DanteAmor. Your solution works perfectly.

The marked solution post has been switched accordingly.

@DogsbodyBoy: In your future questions, please mark the post as the solution that answered your question instead of your feedback post, so it will help future readers. There is no further action is required in this thread.