Highlighting Closest Date

Hi All,

Can someone help me out to create VBA for me?
Have a spreadsheet named "Toner" In column A showing dates of all Fridays in a year. So every Friday I`m putting stock levels in it. So what I am trying to achieve is to highlight the row closest to the Friday in the week when spreadsheet is opened. So if I open it now (30-04-2019) it would highlight in red the row with date 03-05-2019 up till column BC. Hope that makes sense

thanks in advance!

Hi there,

See if the attached workbook gets you moving in the right direction - it uses the following formula to specify the Conditional Formatting of Cell A2:

=AND($A2 - INT(NOW()) < 7,  $A2 - INT(NOW()) >= 0)

Hope this helps - please let me know how you get on.

Regards,

Greg M

Why use INT(NOW()) ?

TODAY() gives the same result.

Hope this helps.

Pete

Hi Greg,

Works like magic! And I thought I will not be able to achieve this without a macro Thank you!

Hi Pete,

tried to swap Now() with Today() and it highlights 22-06-2019 instead of 03-05-2019. Not sure what I did wrong

Hi again,

Many thanks for your feedback.

You're welcome - glad I was able to help.

Regards,

Greg M