Strikethrough code to activate when end date is past.

Hi Guys,

I have this code below, which someone from this forum kindly kindly gave to me for my Worksheet code. What it does is check the value in a dropdown list in column I and if it says "Cancelled" then strikethrough columns A-K of that row.

What I wanted to know is this, in cell N2 has the formula to show todays date. In column D9 onwards is an end date. How can I amend the code below so that it also checks to see if the date in Column D9 onwards is greater than N2 and if so strikethrough columns A-K in that row?

Would it be in the form of an OR statement in the code below?

Thanks in advance.

Please Login or Register  to view this content.

Bump. No response.

Not an OR but more IF THEN tests

Please Login or Register  to view this content.

If not as expected post small example file.

Can you not use Conditional formatting for this?

Hi Andy,

Thanks for your help, unfortunately that added code did not work and I have used up the allocated 3 Coditional Formatting.

Maybe we need to change the code slightly so that it looks at the range ("D9:D34"). I have saved the code in the worksheet code and have attached it so that you can see what I am trying to achieve.

Thanks again for your support.

I still not sure which cell should be check for a date when a cell in column I is changed.

But this checks the date on the same row.

Please Login or Register  to view this content.

Hi Andy thanks again for your help.

Basically if the end date is cells D9-D34 has passed the 'Today's Date' formula in N2 then I wanted to 'strikethrough' Columns A-K, so that I can tell which dates have passed and which ones have not.

I hope I have managed to explain myself correctly, appogies in advance if I am still confusing you.

Can this be done?

This will strike through records that have end date older than date in N2.

Please Login or Register  to view this content.

Maybe this would be best done only when the workbook is opened rather then within the change event?

Hi Andy,

Once again thank you for all of your help. I have added your last piece of code to my original code, I just wanted to check with you if I have placed it in the right place, it works fine but my VBA knowledge is serverely limited and I don't know if it needs tidying up or not.

If it is fine then I can make this thread solved.

Please Login or Register  to view this content.

Where you have placed it the code will run everytime something in column 9 changes.
If you are okay with that then if it works your done.

But you may only want to run the code once, when the workbook opens.
In which case you need to move the code out of the Worksheet_Change event and place it in a routine within a standard code module. The add a line to the Workbook_Open event that calls the new routine.

Originally Posted by Andy Pope

Where you have placed it the code will run everytime something in column 9 changes.
If you are okay with that then if it works your done.

But you may only want to run the code once, when the workbook opens.
In which case you need to move the code out of the Worksheet_Change event and place it in a routine within a standard code module. The add a line to the Workbook_Open event that calls the new routine.

Hi Andy thanks for that top tip, I did not konw of the Workbook_Open event. I shall do this and that should be fine.

Again thank you so much for sparing the time to help me with my problem, I can now mark it solved.