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

i plan to run the code every time a workbook is opened,this will be a
workbook which is used daily
Bob Phillips wrote:
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

i plan to run the code every time a workbook is opened,this will be a
workbook which is used daily
Bob Phillips wrote:
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

Nas,

I am not being awkward in my questioning, but I think that whatever you do
to make sure that they run your code could be applied to making sure they
open the review workbook. So please accept my questions in that light.

So you say that you will run the code in a workbook which is used daily. Why
not just add code there to open the review workbook.

It will all be easier than reading a closed workbook.

Bob

"nas171" wrote in message
ups.com...
i plan to run the code every time a workbook is opened,this will be a
workbook which is used daily
Bob Phillips wrote:
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