I have a feeling that this one is very simple, but I can't crack it! And unfortunately I can't attach an example file because it is highly confidential and would take forever to scrub it clean, so I'll do my best in describing it.

On worksheet A I have a series of dates and a location:

Date Location
05/07/2014 Zoo
09/07/2014 Farm
12/07/2014 Cinema

This is a series of trips that are planned to occur. In order to occur there is a criteria which is recorded seperately and is coded as '1' = criteria were broken and '2' = criteria were not broken. The criteria for each trip must be met from the date of the last trip until the day before the next trip - so using the above example, to get the farm, the criteria must be met from 05/07/2014 until 08/07/2014. In practical terms, there must be no '1' in the desingated column that records the criteria - fairly simple.

But there is an allowance of one case of the criteria being broken, which is recorded seperately ('1' = allowance used and '2' allowance not used). This allowance resets after every potential trip (whether the trip was earned or not). This is all displayed as a 'status' on Worksheet B.

So in terms of practical VB code I'm looking for:
Start with row A on worksheet A - copy the date and location onto worksheet B (the next trip)
On opening the file, take the date of the last trip (or in the case of the first trip, the designated start date)
Look into Column J (between date of last trip and date of next trip) to see if allowance has been used (a '1')
Look into column K (between the two dates) to see if the criteria has been broken on more than one ocasison (two '1')
OR if possible - compare column J and K (between the two dates) to see if a criteria was broken AFTER the allowance has been used

Then, look to see if today's date is the same date as the next trip, if it is, move the reference down to the next rown (A3, A4, etc) on Worksheet A so that the status now reflects the next trip, and the code now looks between the two new dates.

I hope that makes sense? I have an idea on how to do most of it, but its the comparing between two dates and the different criteria that is stmuping me.

Thanks for any help.