Next Date Macro

[samsilverman]

I'm using Excel 2007.

Hey is there a way to have a macro recognize the next date out from today?

The dates I need the macro to look through would be distributed in a single column (F) comprised of about 74 rows. All the dates in that column are EOMONTH dates either 3, 6 or 12 months out from a date a task is performed. That task-performed date is located in the same row of column E.

Just need something to highlight what's coming next (ie within 3 months).

As always, thank you very much for any assistance.

 

[samsilverman]

I Actually figured it out through imagination and snooping. Here's what I did if it will help anyone. If you are an "expert," I'd love to know what you think of the solution.

1. Column E, F and H are set to date format.

2. Column F is set to display a date base on what column H displays. The formula for column F is:
=IF(ISBLANK(H7),"",IF(H7="As Needed","",IF(H7="Other","",IF(H7="Annual",EOMONTH(E7,12),IF(H7="Semi-Annual",EOMONTH(E7,6),IF(H7="Quarterly",EOMONTH(E7,3)))))))

3. I chose Conditional Formatting/New Rule/Format Only Cells That Contain

4. Using the "Cell Value" "Between" option, I typed in =TODAY() for the first value and =EOMONTH(TODAY(),3) for the second value. I then formatted the font to BOLD and the cell to yellow.

5. I then applied it to =$F$6:$F$79. This identifies anything in that range that is due in the next 3 months.

6. I created another rule like the one above but used =EOMONTH(TODAY(),3) for the first value and =EOMONTH(TODAY(),6) for the second value. I applied it to the same range which identifies anything in that range that is due in the next 3-6 months.

7. I opted not to format for 12 months because I see 98% of my clients at least semi-annually.

This is a great forum. Thanks to the many who have helped me learn and think.