workbook/sheet event macro

[Guest]

In a w/book I am having a table contains date of births,and death dates of
the persons in separate columns.For some persons in the list niether date of
births nor death dates are not available,due to lack of information,the
relevant cell data is blank.I am looking for a event macro :- Whenever I open
the w/book a message box be populated informing me the upcoming birth
days(next 30days) and death anniversary(next 30 days) of persosons in my
list.another Msg.box with "happy birth day" to persons whose birth day is
today.Both boxes with ' ok' button upon closing macro should end.Col .g2:g200
contains date of births in dd-mmm- yyyy format and col h2:h250 contains date
of death.

 

[Bob Phillips]

I would not use a message box personally, as I think they are an annoyance.
Why not just conditionally format them, then the visual cue is present as
long as it is applicable?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Mr.Bob ! Can you eloborate your answer as I could not under stand how
conditional format be applied ? and what is visual cue ?

 

[Bob Phillips]

Tungana,

Simple bit first. A visual cue is something that is designed to catch your
eye, so in this instance it would be colouring upcoming birthdays in green,
and upcoming death anniversaries in red say.

By using conditional formatting, you can obtain this visual cue, but it can
be automatic, it will happen when those date come into view, and also can be
made to switch off when past.

Let's say the name is in column A, date of birth in column B, and death in
column C, you could do it this way

Select column B, starting at row 2
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(B2<>"", B2<=TODAY()+30,B2>=TODAY())
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour, green
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =AND(C2<>"", C2<=TODAY()+30,C2>=TODAY())
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour, red
OK
OK


That should do it

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks for your help and advice.

Tungana,

Simple bit first. A visual cue is something that is designed to catch your
eye, so in this instance it would be colouring upcoming birthdays in green,
and upcoming death anniversaries in red say.

By using conditional formatting, you can obtain this visual cue, but it can
be automatic, it will happen when those date come into view, and also can be
made to switch off when past.

Let's say the name is in column A, date of birth in column B, and death in
column C, you could do it this way

Select column B, starting at row 2
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(B2<>"", B2<=TODAY()+30,B2>=TODAY())
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour, green
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =AND(C2<>"", C2<=TODAY()+30,C2>=TODAY())
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour, red
OK
OK


That should do it

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)