Automatic Comment - Date Overdue

[Mr Nolen]

Hello good people of MrExcel,

I need a way for Excel to automatically create a comment "Comment Generated on (Today's date)" in a specific field, let's say "Q9", when the date in field "U9" is -180 days or more from today's date or rather when 180 days or more have passed. I have been searching google and these forums, and I've found something similar, but not quite what I needed. Any help is appreciated!

​

 

[Mr Nolen]

OK this should do it.

The Below strips all previous comments, and writes new comments against overdue items. It ignores blank cells and moves to the next cell.

re reading you first post your dates are in column u, so change instances of q to u, and 17 to 21 if these are the field you use.

Sub commentoverdue()
Dim a As Range
Dim LR As Long
Set a = Range("q1")
LR = Cells(Rows.Count, 17).End(xlUp).Row
ActiveSheet.Cells.ClearComments
For i = 1 To LR
'If ActiveSheet.Range("q" & i) <> "" Then
If DateDiff("d", Range("q" & i), Date) > 180 And ActiveSheet.Range("q" & i) <> "" Then
With ActiveSheet.Range("q" & i).AddComment
 .Visible = False
 .Text "reviewed on " & Date
End With
End If
Next i
End Sub

Let me know if this does it for you.

Cheers.

Dan.

Dan,

This is perfect! Thank you! I was able to get it working with that code. I just have one last question, is there a way to get it to only apply to "Q9" through "Q234" or ignore blank cells. It seems to stop working after hitting a cell without a date.

 

[Mr Nolen]

I apologize, it is definitely ignoring blank cells. I had previous data entered that was blocking it. Thank you again!