I attached the sheet in which I would like this to apply, particularly because it already contains some code under the Sub Worksheet_Calculate() and I wanted to make sure it got merged with this new code ok.

Basically I just want a macro to Check M2:M2000 and see if any of the dates that appear there are older than 30 days. If they are then I would like it to put up a MsgBox displaying any overdue CARs and would like the msgbox to display the CAR # in column A associated with the overdue date in Column M.

For example, looking at the attached sheet- the msgbox would pop up when the user selects the log sheet and it would say:

"Todays Date is 8/28/2009
CAR # 236 was due on 7/26/2009 and is now overdue!
CAR # 239 was due on 7/24/2009 and is now overdue!"

and then the user would click "ok" to get out of the msgbox

I appreciate the help!









Private Sub Worksheet_Calculate()
    Dim cell        As Range

    For Each cell In Intersect(Me.UsedRange, Range("CY2:CY2000"))
        Select Case cell.Value
            Case Empty
                cell.Interior.ColorIndex = xlColorIndexNone
            Case "Pending Approval"
                cell.Interior.ColorIndex = 38
            Case "Open"
                cell.Interior.ColorIndex = 45
            Case "Pending Close"
                cell.Interior.ColorIndex = 6
            Case "Closed"
                cell.Interior.ColorIndex = 35
        End Select
    Next cell
End Sub