Excel VBA add comment on change to cell value

[jfreitag]

I have found a couple of posts that try and help with this problem but none are working that way I need them too. I am looking to have a select few cells generate a comment when the user changes their value. I don't want the entire sheet to be affected as it will only apply to the header row of columns J:P.

I'm not sure if it matters but I have the worksheet protected and the cells in question are unlocked.

Below is an example of the code I'm trying to get to work.

Thank you for any help you can offer!

Option Explicit
Public preValue As Variant
Private Sub worksheet_change(ByVal Target As Range)
End Sub
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format _
(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub

 

[jfreitag]

It took a little more tweaking but I figured it out.

Option Explicit
Public preValue As Variant
Private Sub worksheet_change(ByVal Target As Range)
If Intersect(Target, Range("J4:P4")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & Chr(10) & "Modified " & Format _
(Date, "mm-dd-yyyy") & Chr(10) & Chr(10) & "By " & Environ("UserName")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub