I have managed to find a similar macro on the net that I have customised for my own use, however there are certain aspects that are beyond my capability of amending.
This is the code so far:
Option Explicit
Sub ReportsDue()
Dim counter As Long
Dim iUpdates As Integer
Dim rng As Range
Application.ScreenUpdating = False
'Macro to run if the time of now matches any of the times in column D
Sheets("Projects").Activate
iUpdates = WorksheetFunction.CountIf(Columns(4), TimeValue("08:00:00 AM"))
'TimeValue needs to match the current time of Now(), not specifically 08:00
If iUpdates = 0 Then Sheets("Projects").Activate
For counter = 1 To 59
Set rng = Range("D" & counter)
If Not IsEmpty(rng) Then
'Need to add in and(Range("BS:BS"),<=Date,(Range("BX:BX"),>=Date))
MsgBox _
iUpdates & " Reports are due out:" & vbCrLf & _
vbCrLf & _
"Project " & rng.Offset(, -3).Value & vbCrLf & _
vbCrLf & _
"Owner: " & rng.Offset(, -2).Value & vbCrLf & _
vbCrLf & _
"Due by: " & rng.Value & vbCrLf & _
vbCrLf
'Last value is to be formatted as a time - currently shows decimal value
End If
Next counter
Application.ScreenUpdating = True
End Sub
The message box is on a worksheet I have open every day, all day, and is designed to alert me to certain reports that need sending out by certain times.
On my worksheet, column D has the time that the report is due out by. Col A has the Project name, and column B has the owner of that project.
Col BS and BX have the start and finish dates for the project respectively.
I want the message box to only alert me to any project where todays date is between the dates in BS and BX, Column D is not blank, and of Now() matches the time in column D.
So through the day, I might get a message box at 08:00 to alert of project A, B and C, and a 2nd message box at 09:00 to alert of projects D, E and F.
Although I have this workbook open all day, I'm not specifically looking at it, so conditional formatting would get missed - hence I need a box that I have to manually click off.
Any help would be greatly appreciated as always
Bookmarks