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.

Ferg15989

[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