Selected Answer
Tom
As an alternative to putting a date in column W (which you seem to have a macro for- if not, please use the method in Don's tutorial here: Automatically Timestamp Entries in Excel ), in the attached file I've added the macro below which will print any comments in a date range into VB Project Explorer's Immediate Window:
Private Sub FilterComments()
Dim wComm As CommentThreaded, Sep As String
Dim StrtDt As Date, EndDt As Date, n As Large
' get/ test start date from user
StrtDt = InputBox("Enter earliest date to filter comments(and replies) in column W from:", "Filter date(1)", Date - 7)
If Not IsDate(StrtDt) Or StrtDt > Date Then MsgBox "Try again with a proper start date": Exit Sub
' get/ test start date from user (default-today)
EndDt = InputBox("Enter the last date to filter on:", "Filter date(1)", Date)
If Not IsDate(EndDt) Or EndDt < StrtDt Then MsgBox "Try again with a proper end date": Exit Sub
' define a text separator
Sep = "; "
' run through comments
For Each wComm In ActiveSheet.CommentsThreaded
With wComm
' only check if in column W
If InStr(.Parent.Address, "$W$") = 1 Then
' if in column W, give address and details
Debug.Print .Parent.Address & Sep & .Date & Sep & .Author.Name & Sep & .Text
' loop through any replies...
If .Replies.Count > 0 Then
For n = 1 To .Replies.Count
With .Replies(n)
' ... checking that are in the user date range
If .Date >= StrtDt And .Date < EndDt + 1 Then
'... is so, print out (or do something else)
Debug.Print "Reply " & n & Sep & .Date & Sep & .Author.Name & Sep & .Text
End If
End With
Next n
End If
End If
End With
Next wComm
End Sub
Run it from VB Project Explorer and you should get results like this (from your file) using the (current) default Start / End dates of 02 Jan and today (where first item in bold below is the cell address...
$W$5; 08/01/2023 11:23:50; Tom*** ******; Here I want add comments
Reply 1; 08/01/2023 11:24:14; Tom*** ******; And then add another comment
Reply 2; 08/01/2023 11:24:54; Tom*** ******; And I want in column W the date when the comment was added
... and the *** ****** portions have been added, just to redact some personal info)
You can change the dates (e.g. to get only items from 08 Jan).
Where I've got the first bold Debug.Print line above:
Debug.Print .Parent.Address & Sep & .Date & Sep & .Author.Name & Sep & .Text
you could change that to write cells from columns A to W of that row to another worksheet and concatenate the replies to the new W cell so you have a filtered results page.
Hope this helps (if so, please remember to mark this Answer as Selected). If you need to know how to add a results sheet and output items, name it etc., kindly ask a new question.