Date Comparison using VBA Excel 2010

Question

I have the following code to compare two dates within a workbook.  The dates are in different worksheets and utilize named ranges.  When I attempt to test the code, I receive a "compile error: Invalid qualifier" message.  I need this to compare the dates to insure that the report ran correctly as it is generated from other processes.  I have searched the web and other MS Communities and cannot find a fix.  Any help in setting me on the right path would be greatly appreciated.

Option Explicit

Sub Workbook_Open()

Dim DateCompare As Date       '=Today() on a summary page

Dim DateCompare2 As Date     '=Automatically created date on a different worksheet within the workbook

    If (Range(DateCompare.Date).Value = Range(DateCompare2.Date).Value) Then

        Exit Sub

    Else

        MessageBox

    End If

End Sub

Sub MessageBox()

OutPut = MsgBox("The Report did NOT run properly!" & Chr(10) & _

            Chr(10) & _

            "Please Call: John Doe at (800) ***-****" & Chr(10) & _

            "                     Jane Doe at (800) ***-****", vbExclamation, "Report Update")

End Sub

Answer 1

Hi,

You have 2 problems.

1. A named range doesn't have a 'Date' property. It can contain a date value but that's not the same as a property which you're trying to assign.
2. The named range goes in quotes.

Try this.

Sub Workbook_Open()

 Dim DateCompare As Date       '=Today() on a summary page

 Dim DateCompare2 As Date     '=Automatically created date on a different worksheet within the workbook

     If (Range("DateCompare").Value = Range("DateCompare2").Value) Then

         Exit Sub

     Else

         MessageBox

     End If

 End Sub

 Sub MessageBox()

 Dim OutPut

 OutPut = MsgBox("The Report did NOT run properly!" & Chr(10) & _

             Chr(10) & _

             "Please Call: John Doe at (800) ***-****" & Chr(10) & _

             "                     Jane Doe at (800) "***_****", vbExclamation, "Report Update")

 End Sub

Answer 2

Excellent Mike H.  This works for me.  I initially had a similar version, however, I was missing the quotes for the named ranges.

Thank you for your quick response.

Al S.