Friends
Hi can anyone please help me? I need to set up a reminder in excel. I have a workbook containing a sheet that contains all our permits and inspection expiration dates for our trucking company. i would like to have a reminder telling me 5 days ahead of the due day. Also would like a pop up to show when i open that workbook. Any help will be greatly appreciated!
Thanks for your time and efforts

5 replies

Following is some code which should guide you in the right direction:-

Private Sub Workbook_Open()
Dim lr As Long
Dim cell As Range
lr = Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Range("E2:E" & lr)
If cell.Value = [Today()] + 5 Then
cell.Interior.ColorIndex = 6
End If
Next
MsgBox "The licenses and permits high-lighted in yellow are due in five days' time.", vbExclamation, "WARNING!"
End Sub

The code assumes that the dates are in Column E.

The code is a Workbook_Open event and needs to be placed in the Work Book module. So, right click on the relevant sheet tab, select "view code" and in the VB Editor that opens, double click on "ThisWorkbook" (over to the left) then paste the above code. The code will execute every time that the Work Book is opened.

The above should give you something to play with and adjust to suit your actual situation.

P.S.: In case you may want to high-light the entire row of data and have a message box pop up also, then the following may help:-

Private Sub Workbook_Open()
Dim lr As Long
Dim lCol As Long
Dim cell As Range
lr = Range("E" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
For Each cell In Range("E2:E" & lr)
If cell.Value = [Today()] + 5 Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Interior.ColorIndex = 6
End If
Next
MsgBox "The licenses and permits high-lighted in yellow are due in five days' time.", vbExclamation, "WARNING!"
End Sub

Thanks for your help! looks like its what i was looking for. Please is it possible to add a command button and have it reset the dates that are updated and inspected? Also i'm having an issue with some cells that are getting high-lighted yellow wen i start entering data in the row. What do u think?...Thanks a million for your time and effort!!!!

Please is it possible to add a command button and have it reset the dates that are updated and inspected?

What exactly do you mean by "reset"?

Also i'm having an issue with some cells that are getting high-lighted yellow when i start entering data in the row.

What is the issue? Can you please upload a sample of your work book. There may be other formulae interfering with the whole process. You can upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare then post the link to your file back here. Please be careful with any sensitive data.

Good evening!
Thanks for your time and efforts! below should be a link containing a sample of my workbook. I'm not sure the link will work because that is still new to me. Anyways if u open the workbook, in the reminders sheet i have some data in some cells. What i need to do is, if the date is within 5 days of the expiration date i want that row to be high-lighted and stay high-lighted till someone removes the high-lighted color or checks that row so its means the inspections or permits are updated. is it possible to do that with a command button? If so please help me or lead me in the right direction. Thanks for your time and efforts!!!!

Sub CheckStuff()
Dim lr As Long
Dim cell As Range
lr = Range("F" & Rows.Count).End(xlUp).Row
For Each cell In Range("F2:F" & lr)
If cell.Value <> vbNullString Then
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next
End Sub

you'll see that I have placed the above code in a standard module and assigned the code to the button on the "Reminders" sheet. I have also added Column F into the fray and headed it "Checked By". Once all high-lighted rows have been checked, type a name (or use a data validation drop down with names in it) in Column F beside the high-lighted row and click on the button. The high-light colour will disappear until the next year.

Column F will also give you a permanent record of who checked that all is in order.

I have also placed the second code from my post #1 in the Work Book module (via the "Reminders" sheet) so the permits etc. that are due in five days' time will be high-lighted once the work book is opened.