Pop-up message on Desktop

[That's Confidential]

Don't know if this is the right NG to post this message in, but thought I'd
try! If it ain't could somebody please point me in the right direction?

Within the cells in Column D in one of my Spreadsheet docs, some of the
cells will be shaded red and their date will be prior to today's date (that
is to say the date when the spreadsheet is opened!)

Now, what I would like is, when there are any cells within this column which
are shaded red or are past that day's date, I would like a message to pop up
on the Desktop saying something like "You have some accounts to be deleted!"
Furthermore, even if the Excel spreadsheet hasn't been used that day, I
would like it to still pop up with the message!

Any ideas?

NB: This spreadsheet will be used on my work's computer where a lot of
priviledges or barred etc.....

 

[Guest]

Untested code

Sub datechk(
Application.ScreenUpdating = Fals
For a = 1 To ActiveSheet.UsedRange.SpecialCells(xlLastCell).Ro
If Cells(a, 4).Value < Date Then GoTo hi
Next
MsgBox ("Accounts appear to be in order"
Application.ScreenUpdating = Tru
Exit Su
hit
MsgBox ("You have some accounts to be deleted!"

Application.ScreenUpdating = Tru
End Su

HTH

 

[Paulw2k]

Every time the workbook is opened the message will appear if such a
condition exits

The code should be in THISWORKBOOK module.

Private Sub Workbook_Open()

Dim Rng As Range
Dim lNotCurrentDate As Long

Set Rng = Sheets("AppropriateSheetName").Columns("D")
lNotCurrentDate = WorksheetFunction.CountIf(Rng, "<" & Date)
If lNotCurrentDate > 0 Then MsgBox "You have some accounts to be
deleted!"

End Sub

 

[That's Confidential]

How do you go about putting this code into your workbook? I'm not too sure
how to insert VBA into a spreadsheet?

 

[Guest]

One easy way if your new to this is to start the macro recorder (tools-macros-record new macro) select a cell ans then stop the recording. Then edit macro1, and copy the code from the post over all of the module contents. Run at will
Alternatively, you will need to display your VBA buttons via the view menu, and insert into either a module or this workbook (open) for automated running

Hope this helps.