Update cell on sheet change - w/correct file

[RKFerguson]

Attached is the "xlsm" file that I should have attached to my first post.

It contains several methods that I am testing, but the one I am having trouble with is the VBA to update a cell when the cell at a target address changes.

Thanks much.

FergBPTrack test.xlsm

[Paul_Hossler]

Maybe something like this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("K3:K10")
    
    If Application.Intersect(KeyCells, Target) Is Nothing Then Exit Sub
'     MsgBox "Cell " & Target.Address & " has changed."
    
    Target.Offset(0, 1).Value = Target.Offset(0, -4).Value
End Sub

[RKFerguson]

Maybe something like this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("K3:K10")
    
    If Application.Intersect(KeyCells, Target) Is Nothing Then Exit Sub
'     MsgBox "Cell " & Target.Address & " has changed."
    
    Target.Offset(0, 1).Value = Target.Offset(0, -4).Value
End Sub

[RKFerguson]

Paul,

Thanks for the quick response. I don't want/need to show an alert with the cell(s) change. I was just using the MsgBox so I could see the value of Target.Address.

Thank you also for the code idea/approach. I can't pretend that I fully understand it, but I will try it and study it.

Most appreciative.

Ferg

[Paul_Hossler]

Yea, I commented out the MsgBox

To step through in words ...

If the changed cell (Target) is not one of the key cells, then exit (you had that)

1 column to the right and in the same row of the Target cell, put the value from the same row, and 4 columns to the left

Actually, I was a little careless ... You should disable events before doing something that might re-trigger the event handler

Didn't matter in this case, but it's good practice

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim KeyCells As Range 
     ' The variable KeyCells contains the cells that will
     ' cause an alert when they are changed.
    Set KeyCells = Range("K3:K10") 
     
    If Application.Intersect(KeyCells, Target) Is Nothing Then Exit Sub 
     '     MsgBox "Cell " & Target.Address & " has changed."
     Application.EnableEvents = False
    Target.Offset(0, 1).Value = Target.Offset(0, -4).Value 
     Application.EnableEvents = True

End Sub