Add comment if column contains "Yes" value

[zackete]

Is there some VBA code I can use to automatically add a comment in column H if the cell in column D has the text "Yes"?
So if D15 says "Yes" H15 would include a comment saying something like "Completed" and a date stamp?


Also if D15 for whatever reason stops saying "Yes", once it value changes to "Yes" again the old comment should not be deleted.
That way I can see how many times it was moved to yes and when.

Thanks in advance!

 

[Zot]

You should update your profile to let helpers know what version your Excel is. With Excel 365, the previously called Comment is now called Note. Excel 365 has both Comment and Note.

If you are using older version. Put this in worksheet module.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strDate As String
Dim OldComment, NewComment
Dim rngCmt As Range, cell As Range

Set rngCmt = Range("D:D")
OldComment = ""
NewComment = ""
strDate = "dd-mmm-yy"

If Not Intersect(Target, rngCmt) Is Nothing Then
    If Target Like "*Yes*" Then
        Set cell = Target.Offset(0, 4)
        On Error Resume Next
        OldComment = cell.Comment.Text
        If Not Err.Number = 0 Then cell.AddComment
        On Error GoTo 0
        NewComment = Format(Now, strDate) & "Completed" & Chr(10) & OldComment
        cell.Comment.Text Text:=NewComment
    End If
End If

End Sub

I noticed the comment line is up to 5 only. I'm not sure if this the limitation.

 

[zackete]

Hi Zot, thanks to the code and the tip. I updated my information (using 365 atm).

The code works when I manually input "Yes" in the cell, however the cells I have in D:D have an IFS formula that adds "Yes" based on cell conditions.
The code seems not compatible with that and comments aren't added when the cell changes to "Yes". Sorry, should've specified that before.

 

[Zot]

A change by formula will not trigger Change event but Calculate. Therefore, for anyone to help, more info needed:
1) An example on how your sheet looks like.
2) In column D, would you have more than one Yes at any particular time?
3) What trigger the change in column D?