Yes and No MsgBox when the date is the same as today.

[KyleJackMorrison]

I have a list of names in cells A1:A10
I also have a list of dates in B1:B10 which are course dates (Formated 12-May-16)

What i'm struggling to do is create a MsgBox to pop up if today's date is in column B.

I would like the box to say "Did <name> pass this course?".

If yes, it would colour the cell Green and keep the date in there.

If no it would colour the cell Red and keep the date in there.

Any help would be much appreciated

Regards,
Kyle

 

[Jon von der Heyden]

When should the message pop up? Will it be for a single record, or will it spin through each record, and pop up a message repeatedly until each record has been covered?

The basics are:

To create a Yes No pop-up, and trap the result:

Dim msgResult   As VbMsgBoxResult


msgResult = MsgBox(Prompt:="your_message", Buttons:=vbYesNo + vbQuestion, Title:="your_title")

To handle the result:

If msgResult = vbYes Then
    'do this
Else
    'do that
End If

To change a cell fill color:

Range("A1").Interior.ColorIndex = 3 'red

 

[KyleJackMorrison]

Jon von der Heyden :

Thank you!

The data will be already inputted on the sheet. So if possible i would like the code to either search when the sheet gets activated, or search after new data has been entered.

Here is the tricky part:!

If the code searches the date, finds the date, and colours it either yes or no. But i don't want it popping up with the same message everytime i re enter data or click the sheet.

Is there a way to have the MsgBox pop up only once.

Regards,
Kyle