workbook/sheet event macro
 

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.

workbook/sheet event macro
 

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)

"TUNGANA KURMA RAJU" wrote in
message ...
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.

workbook/sheet event macro
 

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" wrote:

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)

"TUNGANA KURMA RAJU" wrote in
message ...
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.

workbook/sheet event macro
 

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 FormatConditional 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)

"TUNGANA KURMA RAJU" wrote in
message ...
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" wrote:

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)

"TUNGANA KURMA RAJU" wrote
in
message ...
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.

workbook/sheet event macro
 

Thanks for your help and advice.

"Bob Phillips" wrote:

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 FormatConditional 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)

"TUNGANA KURMA RAJU" wrote in
message ...
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" wrote:

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)

"TUNGANA KURMA RAJU" wrote
in
message ...
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.