Formatting, Highlighting Travel Times Conflicts

[Rishek]

So I've been building a series of macros to check a schedule. This crosscheck takes the form of a workbook with a list of everyone's names, commitments and what times they are where.

Here is a sample workbook with macros: 20000

When you run the "Timesplit" macro, there are a number of conditional formatting checks that run to find conflicts and make the thing easy to read. Since the locations of each event are often a 15 to 25 minute drive, one of the macros checks to see whether any two consecutive events are in different locations and if they are, it highlights the first location in yellow:
The following code is within the "timesplit" macro in the workbook.


With .Columns("D:D")
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A1=$A2,$D1<>$D2,$F1>=$E2)"
.FormatConditions(1).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = 6
.TintAndShade = 0
End With
.Range("D1").FormatConditions.Delete


This is great, except a number of locations ARE right next to each other, leading to false positives which in turn has led to ignoring the warnings.

So I have two questions/requests:

1. How do I exclude certain location pairs from being formatted? [WH, ABOT, Costume Shop, WBP, and Pavilion] are all next to one another as are [RHE and RHW]

2. It'd be even better if the formatting was applied to events in different locations that were less than 15 minutes apart.

I know this is less coding and more formula work, but I can't quite conceptualize how best to format this.

Many thanks to everyone who has helped me over the past few months!

[mdmackillop]

Since you only have 22 locations, plot them on a grid and provide Northings and Eastings for each, the distance between each can be calculated and a time factor applied. A UDF can apply these differences to the table as attached.

[Rishek]

Thanks! I'll have a play around with this when I have a bit more time. I think it may be slightly more than I need, but I'll give it a whirl. Have more than 22 locations, but it won't be so many that this is impossible. Only the question then of how I tie the conditional formatting to the results.

[mdmackillop]

You could return time in 10/15 minute intervals or whatever, 0,1,2,3 etc. for that purpose. The time columns are custom formatted using the UDF. Column H can be deleted if desired