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