Contract Expiry Dates

[nwarren]

Hello,

In dire need of help!

I work at a company whom has many contracts with other companies and individuals. Each contract contains the effective date and expiration date. Recently we have run into the problem of using someones service who has an expired contract. We have no way of knowing when the contract expires unless we look at a hard/soft copy. We need a way to be notified when the contract is expired and when it is within 30 days of expiration.

So I created a tracker for these contracts and within it is, the vendor/party, effective date, and expiration. Now all i need was the reminder

I did some searching and discovered the conditional formatting. Worked like a charm! Then I realized it wasn't suiting our needs.

What we need:
For the cell to be highlighted when the expiration is within thirty days of the date marked in the cell, NOT within thirty days of current date.
AND For the cell to highlight when current date is the same date (or past current date) of the date marked in the cell.

For Example in the picture I have attached(if it is attached), you see in B2 the effective Date is Nov. 18 2014. This contract expires Nov. 18 2015. What I need to happen is thirty days before Nov. 18 2015, the cell automatically highlights itself blue so when we see this tracker lets say on Oct. 31 2015, we can see we are within thirty days of expiration and we can notify the contracted company for renewal.

Also take a look at B4. Effective date is Nov. 27 2007 the expiration date is Nov. 27 2010. The expiration Date has passed, I need that to automatically change to red after the current date is past the cell date.

I would greatly appreciate if anyone can tell me if what i need done is possible and can walk me through it.

[Arkadi]

Hi,

I was thinking this might best be put in the worksheet_activate event? That way the macro runs whenever this worksheet is activated. Give me your opinion on that... we can also make it run whenever the workbook is opened.
Additionally, do you want the code to highlight the cell blue/red even if there is a renewal and new expiration date (in the other columns)

[bartelba]

HI,

See attached file

Bas

[Arkadi]

I assume bartelba worked with conditional formatting... Which is probably the best and simplest approach. I got a little too focused on the fact it was in the macros section of the forum and handled it that way, but as I mentioned, conditional formatting is probably ideal. However, for my approach, you would go to vba editor (alt-f11), double-click the sheet name that has your data, and paste this code, which would run whenever the worksheet is activated:

Please Login or Register  to view this content.

[nwarren]

Exactly what is the Worksheet activate Event? I am open to anything that will work the way I need it to.

As for the renewal I had played with a code for that as well wasn't 100% sure if I wanted to use it.

[Arkadi]

Ok well a worksheet activate event gets triggered whenever the worksheet that the code is in gets activated, such as opening the workbook, or when you switch from another sheet to the the one in question.

[nwarren]

Hi,

I was thinking this might best be put in the worksheet_activate event? That way the macro runs whenever this worksheet is activated. Give me your opinion on that... we can also make it run whenever the workbook is opened.
Additionally, do you want the code to highlight the cell blue/red even if there is a renewal and new expiration date (in the other columns)

Exactly what is worksheet activate event?

Seems like whenever the workbook is opened is more ideal for it to run.
as for the code for renewal I was contemplating doing that havent decided yet.

[nwarren]

Ok well a worksheet activate event gets triggered whenever the worksheet that the code is in gets activated, such as opening the workbook, or when you switch from another sheet to the the one in question.

Sounds great!! sorry for my multiple post having a slow day with the computer. Will that code do what i need done?

[Arkadi]

If on opening is good enough then go to vba editor (alt-f11), double click "ThisWorkbook" on the left towards the top, and paste this code:

Please Login or Register  to view this content.

[Arkadi]

Both should work...I don't know if you leave your book open for days at a time, and thought of worksheet change... but workbook open ought to do it by the sounds of it... less demanding i suppose.

[nwarren]

If on opening is good enough then go to vba editor (alt-f11), double click "ThisWorkbook" on the left towards the top, and paste this code:

Please Login or Register  to view this content.

after i paste what do i do?

[Arkadi]

Well... #1 change Set ws = worksheets("Sheet1") to reference the actual sheet name that has your data... I hope the dates are in column C as in your example?
Then save the workbook. The macro runs each time you open it again.

You can also test it without having to open simply by hitting the run button in the top menu... the little green arrow that looks like the play button on a stereo.

[nwarren]

Well... #1 change Set ws = worksheets("Sheet1") to reference the actual sheet name that has your data... I hope the dates are in column C as in your example?
Then save the workbook. The macro runs each time you open it again.

You can also test it without having to open simply by hitting the run button in the top menu... the little green arrow that looks like the play button on a stereo.

yes the dates are in column C. I change worksheets or sheet 1 in the set=ws? example the sheet name is contract database

[Arkadi]

so then

Please Login or Register  to view this content.

[nwarren]

so then

Please Login or Register  to view this content.

After I save it, it should be formatted the way i need it correct?

[Arkadi]

If you mean the colours in the cells should be there, no, the code runs when you open the workbook, so save, close, re-open your excel file, and then yes it should be done.

I'll be slow answering for a bit, heading home, but will check in when I arrive to respond to questions if you have any... If you are satisfied, reputation is appreciated but not required, however, please do mark the thread as solved once you are happy with the results.

[nwarren]

this appeared after reopening it

[nwarren]

and this when i clicked Debug
the line reads: If ws.Range("C" & i).Value - 30 < Date And ws.Range("C" & i).Value > Date Then 'if the date in the cell is greater than today, and the value in the cell - 30 days is less than today then...

[nwarren]

also it did not apply to all of column C

[Arkadi]

Are there some blanks in column c between dates?

[nwarren]

yes there are. They are unknown dates

[Arkadi]

Oops, I should have thought of that. Gimme a min to log in on PC, phone is no good for this

[Arkadi]

Ok.... sorry about that... silly mistake on my part

Please Login or Register  to view this content.

[nwarren]

no worries at all. Super appreciate your time.

Now what should I do about the debugging issue?

[nwarren]

I think the new code fixed the debug issue

[Arkadi]

WAIT... bad choice, fixes issue... unless you have a word in by mistake or a letter...

[nwarren]

what's the issue?

[Arkadi]

This is more solid code:

Please Login or Register  to view this content.

Problem was if you ever put a word or letter in column c by mistake or to mark unknown or something, then you'd get an error. This new one takes care of blanks AND letters

[nwarren]

ok! looks like it works. I put in yesterday's date to test it out...it didnt change color. I'm assuming changes won't be made until I close it and reopen it?

[nwarren]

YOU ARE MY SAVIOR!!!!! lets keep in touch I have a feeling I'lll be making a few more of these. You were a great help i really appreciate your time.

[Arkadi]

that's right about change only when you open it... however, we can add the same routine somewhere else if you want it to work whenever you enter a date... that's a worksheet change event...

[nwarren]

Hi again. Hoping you could help me out. Im working with the same document and we have decided to add worksheets to the workbook. I want all of the functions in the first worksheet to work for the rest of the worksheets. (I added 3extra) Do I need a new code for that?

[Arkadi]

I'm cooking dinner but will get back to you, not hard, no worries.

[Arkadi]

Sorry nwarren, got caught up in stuff last night, but give this new code a try:

Please Login or Register  to view this content.

[nwarren]

No worries.

I tried it and it didnt seem to work on the other sheets the original sheet still works

[Arkadi]

So you replaced the code in "ThisWorkbook" from before with this, but only the original sheet changed? Can you provide the current workbook?

[nwarren]

my apologies I put the code only in a sheet not the whole workbook. It works now

[Arkadi]

Oh, lol OK. Great, easy solution