Hi friends,

The following code is working fine till the last day of month but not working for the first day of next month. For ex. There are 31 days in January which is the last day of January. The next date is 01/02/16 the first day of month February. In this case the code is not giving message of tomorrow’s birthday because as per the code the next day is 31+1 = 32 which is not a date. The next day is 1 the first day of February.

So what shall I do to recognize the next day 1 instead of 32. It’s same for each last day of month.

Sub Tomorrow()

Dim x, i

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Worksheets("Access").Activate

Const clngSTART As Long = 8

With Sheets("Access")
x = .Range(.Cells(clngSTART, "N"), .Cells(.Cells(Rows.Count, "O").End(xlUp).Row + 1, .Cells(clngSTART, Columns.Count).End(xlToLeft).Column)).Value
End With

'Message birthdays tomorrow

For i = 1 To UBound(x)
If x(i, 2) <> "-" Then
  If IsDate(x(i, 2) + 1) Then
      If Month(x(i, 2)) = Month(Date) And Day(x(i, 2)) = Day(Date) + 1 Then
MsgBox x(i, 1) & "'s Birthday tomorrow!", 64
End If
End If
End If
Next i

MsgBox "The task completed successfully!", 64

With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub
Any help will be highly appreciated.

Thanking you in anticipation.