Public preValue As Variant 'Declares a variable to be used in both macros 'This is the first line for a macro that runs when a cell is changed. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub 'Limits the change to 1 cell only If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub Target.ClearComments 'Clears any existing comment Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName") 'Adds a new comment with the text. CHR(10) is a return. End Sub 'This code sets the variable preValue when a cell is selected Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub 'Limits code to 1 cell If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub preValue = Target.Value 'Set the variable preValue to the selected cell's value End Sub
Using the above code: The above code Marks changes made in one worksheet and when you scroll over it with you mouse it tells user what changes had been made to that cell. How do I copy those changes and paste them into another cell on the second worksheet. Lets say into A10. I would appreciate any help or if you need anything else from me please let me know.