Rishek
08-05-2017, 04:29 PM
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!
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!