Wookbook_Open Message Giving Count of Records not Closed for More than Specified Number of Days

Gos-C

Active Member
Joined
Apr 11, 2005
Messages
252
Hi all,

I am trying to add a code so that when a user opens his/her workbook he/she gets a message with the number of records opened 30 days or more with a close date. The open date is in column C and the close date is in column AA.

I tried the following (which is not working):

Code:
Private Sub Workbook_Open()
'Sub Test_PastDueClose()
Dim cPast As Long, dRng As Range, cRng As Range
Set dRng = Sheets("LOG").Range("C4:C" & Cells(Rows.Count, 3).End(xlUp).Row)
Set cRng = Sheets("LOG").Range("AA4:AA" & Cells(Rows.Count, 27).End(xlUp).Row)
cPast = 0
For Each cell In dRng
If DateDiff("d", Now(), dRng) >= 30 Then
If cRng = "" Then
cPast = cPast + 1
End If
Next cell

MsgBox "Your log has " & cPast & " records that have past their scheduled close date.  They are highlight RED."

End Sub
Any help, please?

Thank you,
Gos-C
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,095,811
Messages
5,446,633
Members
405,411
Latest member
mar5del

This Week's Hot Topics

Top