Everytime a cell changes in a named range, I want the code to clear existing comments, then record the old value as a new comment. I have to use Worksheet_Calculate because the cell changes based on a formula. I have tried several, methods, but none are working...
Code
Private Sub Worksheet_Calculate()
Dim Target As Range
Dim c As Range
If Not Target Is Nothing Then Exit Sub
For Each c In Range("Colonne8300")
If c.Count > 1 Then Exit Sub
c.ClearComments
c.AddComment.Text Text:="TEST"
Next
End Sub
Display More
In this case, it adds a comment to every cell.
Code
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Auparavant " & preValue & Chr(10) & "Actuellement " _
& Format(Date, "mm-dd-yyyy") & Chr(10) & "Modification par " & 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
Display More
This works only when I change the cell manually.