I have a sheet with risk assesments which need to be revieved every so
often, in column a are the review dates and in column b are the
document names , in column c are the document numbers. I need a vba
code which will search the list and identify which documents need
reviewing 7 days before the review date, then display a message with
the results.I am looking to put this code in to run every time the
workbook is opened.
Thanx in Advance

Nas,

You do not need code, just Conditional Formatting.

Select column A
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(A1=TODAY(),A1<=TODAY()+7)
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

Now all dates from today 7 days forward will be coloured and update as the
date changes.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
oups.com...
I have a sheet with risk assesments which need to be revieved every so
often, in column a are the review dates and in column b are the
document names , in column c are the document numbers. I need a vba
code which will search the list and identify which documents need
reviewing 7 days before the review date, then display a message with
the results.I am looking to put this code in to run every time the
workbook is opened.
Thanx in Advance

Bob
Thanx for the quick reply.I was thinking of using the code to flag up a
message even when the workbook is closed.

Bob Phillips wrote:
Nas,

You do not need code, just Conditional Formatting.

Select column A
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(A1=TODAY(),A1<=TODAY()+7)
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

Now all dates from today 7 days forward will be coloured and update as the
date changes.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
oups.com...
I have a sheet with risk assesments which need to be revieved every so
often, in column a are the review dates and in column b are the
document names , in column c are the document numbers. I need a vba
code which will search the list and identify which documents need
reviewing 7 days before the review date, then display a message with
the results.I am looking to put this code in to run every time the
workbook is opened.
Thanx in Advance

You could do that with a fair bit of VBA code, but wouldn't it be better to
just load this file every day with the CF?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
oups.com...
Bob
Thanx for the quick reply.I was thinking of using the code to flag up a
message even when the workbook is closed.

Bob Phillips wrote:
Nas,

You do not need code, just Conditional Formatting.

Select column A
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(A1=TODAY(),A1<=TODAY()+7)
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

Now all dates from today 7 days forward will be coloured and update as
the
date changes.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
oups.com...
I have a sheet with risk assesments which need to be revieved every so
often, in column a are the review dates and in column b are the
document names , in column c are the document numbers. I need a vba
code which will search the list and identify which documents need
reviewing 7 days before the review date, then display a message with
the results.I am looking to put this code in to run every time the
workbook is opened.
Thanx in Advance

Hi Bob
There are no gaurantees the guys would open this on a daily basis , so
i was trying to get an idiot proof method where by we would not miss
the review dates
Bob Phillips wrote:
You could do that with a fair bit of VBA code, but wouldn't it be better to
just load this file every day with the CF?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
oups.com...
Bob
Thanx for the quick reply.I was thinking of using the code to flag up a
message even when the workbook is closed.

Bob Phillips wrote:
Nas,

You do not need code, just Conditional Formatting.

Select column A
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(A1=TODAY(),A1<=TODAY()+7)
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

Now all dates from today 7 days forward will be coloured and update as
the
date changes.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
oups.com...
I have a sheet with risk assesments which need to be revieved every so
often, in column a are the review dates and in column b are the
document names , in column c are the document numbers. I need a vba
code which will search the list and identify which documents need
reviewing 7 days before the review date, then display a message with
the results.I am looking to put this code in to run every time the
workbook is opened.
Thanx in Advance

Okay, so what idiot-proof way are you envisaging to ensure that they run the
code that does that check?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
ups.com...
Hi Bob
There are no gaurantees the guys would open this on a daily basis , so
i was trying to get an idiot proof method where by we would not miss
the review dates
Bob Phillips wrote:
You could do that with a fair bit of VBA code, but wouldn't it be better
to
just load this file every day with the CF?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
oups.com...
Bob
Thanx for the quick reply.I was thinking of using the code to flag up
a
message even when the workbook is closed.

Bob Phillips wrote:
Nas,

You do not need code, just Conditional Formatting.

Select column A
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(A1=TODAY(),A1<=TODAY()+7)
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK

Now all dates from today 7 days forward will be coloured and update
as
the
date changes.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nas171" wrote in message
oups.com...
I have a sheet with risk assesments which need to be revieved
every so
often, in column a are the review dates and in column b are the
document names , in column c are the document numbers. I need a
vba
code which will search the list and identify which documents need
reviewing 7 days before the review date, then display a message
with
the results.I am looking to put this code in to run every time
the
workbook is opened.
Thanx in Advance