Excel, update cells when clicking on another cell

Question

I have tried a few different bits of VBA found here on SuperUser but not able to get it to work.

I am trying to update a cell when I just click on another cell.

I have data in A43:A54, it's a list of names, there is also a list in B43:B54.

When I click on a cell from A43:A54 I would like to update cell B38 with the contents of the clicked cell along with updating cell B39 with the content from the same row from B43:A54

Eg...

A43 = London, B43 = Glasgow

A44 = Birmingham, B44 = Edinburgh

A45 = Manchester, B45 = Belfast

etc etc etc

If I click the cell A44, I would like cell B38 to change it's content to Birmingham and cell B39 to change it's content to Edinburgh

Thanks

Answer 1

Just add this code to yours sheet's code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A43:A54")) Is Nothing And Target.Cells.count = 1 Then
    Range("B38") = Target.Value
    Range("B39") = Target.Offset(, 1).Value
End If

End Sub

Answer 2

You can combine formulas with VBA.
In Options: Options > Formulas > Enable Iterative Calculations
Formulas:
B38 : =IF(AND(CELL("row")>=43,CELL("row")<=54,CELL("col")=1),CELL("contents"),B38)
B39 : =IFNA(VLOOKUP(B38,$A$43:$B$54,2,FALSE),"")
Code in the sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Intersect(Target, Range("A43:A54")) Is Nothing Then Exit Sub
   Me.Calculate
End Sub

A screenshot: