Update 2 cells when clicking on another cell

[STGlove]

I have tried a few different bits of VBA found here 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.

I have another cell B38 and B39 which I would like to update automatically with the content from the clicked cell.

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 but from B43:A54
Eg...
A43 = London, B43 = Glasgow
A44 = Birmingham, B44 = Edinburgh
A45 = Manchester, B45 = Belfast
etc etc etc
If I just click the cell A44, I would like VBA to update cell B38 and to change it's content to Birmingham and cell B39 to change it's content to Edinburgh

Thank you for reading and replying
STG

 

[kevin9999]

Hi STG and welcome to the Forum

Could I suggest a double-click event rather than simply a changed cell event - which could trigger unexpectedly if you simply move from a cell by another means? Please try the following on a copy of your workbook. The code goes in the sheet code module of the sheet in question - click on the sheet tab name, select View Code and place the code in the window that appears on the right of the screen.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    If Not Intersect(Range("A43:A54"), Target) Is Nothing Then
        Application.EnableEvents = False
        cancel = True
        [B38] = Target
        [B39] = Target.Offset(, 1)
        Application.EnableEvents = True
    End If
End Sub

 

[STGlove]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean) If Not Intersect(Range("A43:A54"), Target) Is Nothing Then Application.EnableEvents = False cancel = True [B38] = Target [B39] = Target.Offset(, 1) Application.EnableEvents = True End If End Sub

Thanks, this works great, most appreciated

 

[kevin9999]

Happy to help, and thanks for the feedback