software

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 quot;happy birth dayquot; 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.

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)

quot;TUNGANA KURMA RAJUquot; gt; wrote in
message ...
gt; In a w/book I am having a table contains date of births,and death dates of
gt; the persons in separate columns.For some persons in the list niether date
of
gt; births nor death dates are not available,due to lack of information,the
gt; relevant cell data is blank.I am looking for a event macro :- Whenever I
open
gt; the w/book a message box be populated informing me the upcoming birth
gt; days(next 30days) and death anniversary(next 30 days) of persosons in my
gt; list.another Msg.box with quot;happy birth dayquot; to persons whose birth day is
gt; today.Both boxes with ' ok' button upon closing macro should end.Col
..g2:g200
gt; contains date of births in dd-mmm- yyyy format and col h2:h250 contains
date
gt; of death.
Mr.Bob ! Can you eloborate your answer as I could not under stand how
conditional format be applied ? and what is visual cue ?

quot;Bob Phillipsquot; wrote:

gt; I would not use a message box personally, as I think they are an annoyance.
gt; Why not just conditionally format them, then the visual cue is present as
gt; long as it is applicable?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;TUNGANA KURMA RAJUquot; gt; wrote in
gt; message ...
gt; gt; In a w/book I am having a table contains date of births,and death dates of
gt; gt; the persons in separate columns.For some persons in the list niether date
gt; of
gt; gt; births nor death dates are not available,due to lack of information,the
gt; gt; relevant cell data is blank.I am looking for a event macro :- Whenever I
gt; open
gt; gt; the w/book a message box be populated informing me the upcoming birth
gt; gt; days(next 30days) and death anniversary(next 30 days) of persosons in my
gt; gt; list.another Msg.box with quot;happy birth dayquot; to persons whose birth day is
gt; gt; today.Both boxes with ' ok' button upon closing macro should end.Col
gt; ..g2:g200
gt; gt; contains date of births in dd-mmm- yyyy format and col h2:h250 contains
gt; date
gt; gt; of death.
gt;
gt;
gt;

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 Formatgt;Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(B2lt;gt;quot;quot;, B2lt;=TODAY() 30,B2gt;=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(C2lt;gt;quot;quot;, C2lt;=TODAY() 30,C2gt;=TODAY())
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour, red
OK
OKThat should do it

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;TUNGANA KURMA RAJUquot; gt; wrote in
message ...
gt; Mr.Bob ! Can you eloborate your answer as I could not under stand how
gt; conditional format be applied ? and what is visual cue ?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; I would not use a message box personally, as I think they are an
annoyance.
gt; gt; Why not just conditionally format them, then the visual cue is present
as
gt; gt; long as it is applicable?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;TUNGANA KURMA RAJUquot; gt; wrote
in
gt; gt; message ...
gt; gt; gt; In a w/book I am having a table contains date of births,and death
dates of
gt; gt; gt; the persons in separate columns.For some persons in the list niether
date
gt; gt; of
gt; gt; gt; births nor death dates are not available,due to lack of
information,the
gt; gt; gt; relevant cell data is blank.I am looking for a event macro :- Whenever
I
gt; gt; open
gt; gt; gt; the w/book a message box be populated informing me the upcoming birth
gt; gt; gt; days(next 30days) and death anniversary(next 30 days) of persosons in
my
gt; gt; gt; list.another Msg.box with quot;happy birth dayquot; to persons whose birth day
is
gt; gt; gt; today.Both boxes with ' ok' button upon closing macro should end.Col
gt; gt; ..g2:g200
gt; gt; gt; contains date of births in dd-mmm- yyyy format and col h2:h250
contains
gt; gt; date
gt; gt; gt; of death.
gt; gt;
gt; gt;
gt; gt;
Thanks for your help and advice.

quot;Bob Phillipsquot; wrote:

gt; Tungana,
gt;
gt; Simple bit first. A visual cue is something that is designed to catch your
gt; eye, so in this instance it would be colouring upcoming birthdays in green,
gt; and upcoming death anniversaries in red say.
gt;
gt; By using conditional formatting, you can obtain this visual cue, but it can
gt; be automatic, it will happen when those date come into view, and also can be
gt; made to switch off when past.
gt;
gt; Let's say the name is in column A, date of birth in column B, and death in
gt; column C, you could do it this way
gt;
gt; Select column B, starting at row 2
gt; Menu Formatgt;Conditional Formatting
gt; Change Condition 1 to Formula Is
gt; Add a formula of =AND(B2lt;gt;quot;quot;, B2lt;=TODAY() 30,B2gt;=TODAY())
gt; Click the Format button
gt; Select the Pattern Tab
gt; Select an appropriate highlighting colour, green
gt; OK
gt; OK
gt;
gt; Second condition
gt;
gt; Click Add
gt; Change Condition 2 to Formula Is
gt; Add a formula of =AND(C2lt;gt;quot;quot;, C2lt;=TODAY() 30,C2gt;=TODAY())
gt; Click the Format button
gt; Select the Pattern Tab
gt; Select an appropriate highlighting colour, red
gt; OK
gt; OK
gt;
gt;
gt; That should do it
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;TUNGANA KURMA RAJUquot; gt; wrote in
gt; message ...
gt; gt; Mr.Bob ! Can you eloborate your answer as I could not under stand how
gt; gt; conditional format be applied ? and what is visual cue ?
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; I would not use a message box personally, as I think they are an
gt; annoyance.
gt; gt; gt; Why not just conditionally format them, then the visual cue is present
gt; as
gt; gt; gt; long as it is applicable?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;TUNGANA KURMA RAJUquot; gt; wrote
gt; in
gt; gt; gt; message ...
gt; gt; gt; gt; In a w/book I am having a table contains date of births,and death
gt; dates of
gt; gt; gt; gt; the persons in separate columns.For some persons in the list niether
gt; date
gt; gt; gt; of
gt; gt; gt; gt; births nor death dates are not available,due to lack of
gt; information,the
gt; gt; gt; gt; relevant cell data is blank.I am looking for a event macro :- Whenever
gt; I
gt; gt; gt; open
gt; gt; gt; gt; the w/book a message box be populated informing me the upcoming birth
gt; gt; gt; gt; days(next 30days) and death anniversary(next 30 days) of persosons in
gt; my
gt; gt; gt; gt; list.another Msg.box with quot;happy birth dayquot; to persons whose birth day
gt; is
gt; gt; gt; gt; today.Both boxes with ' ok' button upon closing macro should end.Col
gt; gt; gt; ..g2:g200
gt; gt; gt; gt; contains date of births in dd-mmm- yyyy format and col h2:h250
gt; contains
gt; gt; gt; date
gt; gt; gt; gt; of death.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;