Macro to search for patterns in a range of dates

Hey all,

I'm not sure how easy this would be to put into a macro, but I was wondering if Excel can check a date and compare it to various criteria.

The worksheet will have dates in a single column, one date per month. I would like a macro to check if the most recently entered date meets any of the following...

1) Is the date (ie the 21st of the month) within five days of the previous month?
2) Is the day (ie Tuesday) the same day as 3 or more of the previous six months?
3) Is the date in the same half of the previous month (ie after the 15th both months)?
4) Is there an alternating pattern of dates (ie 1st, 22nd, 1st, 22nd)?

As an example assume the following data in A1:A6 - 1/12/11, 2/14/11, 3/21/11, 4/5/11, 5/12/11, 6/27/11

Now, the user would enter 7/24/11 in A7 and the system would prompt that it is within 5 days of 6/27/11 (#1 above) and is in the same half of the month as June (#3 above).

Once I get the skeleton of the macro down, I can incorporate (I think) any additional parameters that may arise, but I'm not sure how to check the data in the most recently entered cell for various criteria against the 6 months prior.

I hope this is somewhat clear, but feel free to ask additional questions for clarity.

Thanks so much for your help!

Marcol,

Thanks for the workbook. It's kind of what I'm looking for but would much rather have it in macro form so the cells can't inadvertently be changed. It will ultimately be a message box that pops up after a date has been entered with any patterns found.