Compare Multiple rows and columns for potential clashes

S

ShruggedTundra

New Member
Joined
Nov 24, 2015
Messages
13
Good afternoon,
I'm trying to create a sheet that will identify a clash of resources based on a control button click. we did have one that we though was working until we looked more closely.
The sheet we have is like this.
[TABLE="width: 2051"]
<colgroup><col width="20" style="width: 15pt; mso-width-source: userset; mso-width-alt: 731;" span="2"> <col width="149" style="width: 112pt; mso-width-source: userset; mso-width-alt: 5449;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;" span="2"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;"> <col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;" span="6"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="22" style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="176" style="width: 132pt; mso-width-source: userset; mso-width-alt: 6436;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;" span="7"> <col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;" span="9"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="64" style="width: 48pt;" span="2"> <col width="391" style="width: 293pt; mso-width-source: userset; mso-width-alt: 14299;"> <tbody>[TR]
[TD="class: xl91, width: 20, bgcolor: #92D050"] Complete[/TD]
[TD="class: xl91, width: 20, bgcolor: #92D050"]Atlas[/TD]
[TD="class: xl98, width: 149, bgcolor: #92D050"]Date [/TD]
[TD="class: xl69, width: 40, bgcolor: #92D050"]Time From[/TD]
[TD="class: xl69, width: 40, bgcolor: #92D050"]Time To[/TD]
[TD="class: xl69, width: 52, bgcolor: #92D050"]Duration[/TD]
[TD="class: xl69, width: 65, bgcolor: #92D050"]12hr
 Days or
Nights[/TD]
[TD="class: xl70, width: 85, bgcolor: #C4D79B"]Date Completed[/TD]
[TD="class: xl71, width: 91, bgcolor: #92D050"]Directorate[/TD]
[TD="class: xl72, width: 82, bgcolor: #92D050"]OU 1[/TD]
[TD="class: xl73, width: 86, bgcolor: #C4D79B"]OU 2[/TD]
[TD="class: xl74, width: 92, bgcolor: #C4D79B"]Building No.[/TD]
[TD="class: xl94, width: 33, bgcolor: #C4D79B"] DAYS[/TD]
[TD="class: xl94, width: 19, bgcolor: #C4D79B"] 12 hr[/TD]
[TD="class: xl94, width: 19, bgcolor: #C4D79B"] 5 Cycle[/TD]
[TD="class: xl94, width: 19, bgcolor: #C4D79B"] 6 Cycle[/TD]
[TD="class: xl94, width: 19, bgcolor: #C4D79B"] 5 DD[/TD]
[TD="class: xl94, width: 19, bgcolor: #C4D79B"] 7 DD[/TD]
[TD="class: xl95, width: 19, bgcolor: #C4D79B"]Shift U/K[/TD]
[TD="class: xl71, width: 96, bgcolor: #92D050"]Exercise Type - Existing[/TD]
[TD="class: xl92, width: 22, bgcolor: #92D050"]Type[/TD]
[TD="class: xl75, width: 111, bgcolor: green"]Exercise Type
 New[/TD]
[TD="class: xl74, width: 176, bgcolor: #C4D79B"]Brief Description[/TD]
[TD="class: xl71, width: 47, bgcolor: #92D050"]S&SSM[/TD]
[TD="class: xl71, width: 47, bgcolor: #92D050"]SMC[/TD]
[TD="class: xl71, width: 47, bgcolor: #92D050"]EDT[/TD]
[TD="class: xl71, width: 47, bgcolor: #92D050"]ICC[/TD]
[TD="class: xl71, width: 47, bgcolor: #92D050"]ACP[/TD]
[TD="class: xl71, width: 47, bgcolor: #92D050"]ERC[/TD]
[TD="class: xl92, width: 47, bgcolor: #92D050"]HP&S[/TD]
[TD="class: xl96, width: 33, bgcolor: red"]FT[/TD]
[TD="class: xl96, width: 33, bgcolor: red"]FT[/TD]
[TD="class: xl96, width: 33, bgcolor: red"]FT[/TD]
[TD="class: xl96, width: 33, bgcolor: red"]FT[/TD]
[TD="class: xl96, width: 33, bgcolor: red"]FT[/TD]
[TD="class: xl97, width: 33, bgcolor: blue"]Pol [/TD]
[TD="class: xl97, width: 33, bgcolor: blue"]Pol [/TD]
[TD="class: xl97, width: 33, bgcolor: blue"]Pol [/TD]
[TD="class: xl92, width: 33, bgcolor: #92D050"]ECC[/TD]
[TD="class: xl74, width: 104, bgcolor: #C4D79B"]Lead
 Planner[/TD]
[TD="class: xl76, width: 111, bgcolor: #C4D79B"]Responsible Person[/TD]
[TD="class: xl76, width: 64, bgcolor: #C4D79B"]Site Insp View[/TD]
[TD="class: xl76, width: 64, bgcolor: #C4D79B"]Regulator View[/TD]
[TD="class: xl76, width: 391, bgcolor: #C4D79B"]Notes[/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Fri 01 April 2016[/TD]
[TD="class: xl79, bgcolor: transparent"]16:00[/TD]
[TD="class: xl79, bgcolor: transparent"]12:00[/TD]
[TD="class: xl101, bgcolor: transparent"]20:00[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl80, bgcolor: transparent"]01/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl89, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 96, bgcolor: transparent"]FRIDEX[/TD]
[TD="class: xl85, width: 22, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 111, bgcolor: transparent"]FRIDEX[/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"] [/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Sat 02 April 2016[/TD]
[TD="class: xl79, bgcolor: transparent"]09:00[/TD]
[TD="class: xl79, bgcolor: transparent"]10:00[/TD]
[TD="class: xl101, bgcolor: transparent"]1:00[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl80, bgcolor: transparent"]02/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl89, bgcolor: transparent"]Yes[/TD]
[TD="class: xl84, bgcolor: transparent"]D[/TD]
[TD="class: xl84, bgcolor: transparent"]B[/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]A[/TD]
[TD="class: xl85, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 96, bgcolor: transparent"]DRILL[/TD]
[TD="class: xl85, width: 22, bgcolor: transparent"]D[/TD]
[TD="class: xl85, width: 111, bgcolor: transparent"]UNCOACHED DRILL[/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Planner[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Sat 02 April 2016[/TD]
[TD="class: xl79, bgcolor: transparent"]23:00[/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl101, bgcolor: transparent"]1:00[/TD]
[TD="class: xl102, bgcolor: transparent"]82[/TD]
[TD="class: xl80, bgcolor: transparent"]22/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl89, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]B[/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 96, bgcolor: transparent"]WALKDOWN[/TD]
[TD="class: xl82, width: 22, bgcolor: transparent"]D[/TD]
[TD="class: xl82, width: 111, bgcolor: transparent"]WALKDOWN DRILL[/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"]Yes [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"]Yes [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Planner[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Sun 03 April 2016[/TD]
[TD="class: xl79, bgcolor: transparent"]23:00[/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl101, bgcolor: transparent"]1:00[/TD]
[TD="class: xl102, bgcolor: transparent"]82[/TD]
[TD="class: xl80, bgcolor: transparent"]23/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl89, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]B[/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 96, bgcolor: transparent"]WALKDOWN[/TD]
[TD="class: xl85, width: 22, bgcolor: transparent"]D[/TD]
[TD="class: xl85, width: 111, bgcolor: transparent"]WALKDOWN DRILL[/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"]Yes [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Planner[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Mon 04 April 2016[/TD]
[TD="class: xl78, bgcolor: transparent"]19:00[/TD]
[TD="class: xl78, bgcolor: transparent"]21:00[/TD]
[TD="class: xl101, bgcolor: transparent"]2:00[/TD]
[TD="class: xl102, bgcolor: transparent"]82[/TD]
[TD="class: xl80, bgcolor: transparent"]04/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl88, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"]B[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"]FULL EXERCISE[/TD]
[TD="class: xl81, bgcolor: transparent"]E[/TD]
[TD="class: xl103, bgcolor: transparent"]FULL EXERCISE[/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"]Yes [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Planner[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Tue 05 April 2016[/TD]
[TD="class: xl78, bgcolor: transparent"]08:30[/TD]
[TD="class: xl78, bgcolor: transparent"]16:00[/TD]
[TD="class: xl101, bgcolor: transparent"]7:30[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl80, bgcolor: transparent"]05/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl88, bgcolor: transparent"]Y[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"]Coached Exercise[/TD]
[TD="class: xl81, bgcolor: transparent"]E[/TD]
[TD="class: xl103, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"]Yes [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"]Yes [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Planner[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]4[/TD]
[TD="class: xl77, bgcolor: transparent"]Y[/TD]
[TD="class: xl104, bgcolor: transparent"]Tue 05 April 2016[/TD]
[TD="class: xl105, bgcolor: transparent"]17:00[/TD]
[TD="class: xl105, bgcolor: transparent"]19:00[/TD]
[TD="class: xl106, bgcolor: transparent"]2:00[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl107, bgcolor: transparent"]05/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl87, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl109, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"]Y[/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"]Fire Alarm[/TD]
[TD="class: xl108, bgcolor: transparent"]L[/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl108, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Planner[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl87, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl87, width: 391, bgcolor: transparent"]BN1604A0402[/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Wed 06 April 2016[/TD]
[TD="class: xl78, bgcolor: transparent"]08:30[/TD]
[TD="class: xl78, bgcolor: transparent"]16:00[/TD]
[TD="class: xl101, bgcolor: transparent"]7:30[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl80, bgcolor: transparent"]06/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl88, bgcolor: transparent"]Y[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"]Coached Exercise[/TD]
[TD="class: xl81, bgcolor: transparent"]E[/TD]
[TD="class: xl103, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Planner[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Wed 06 April 2016[/TD]
[TD="class: xl93, width: 40, bgcolor: transparent"]10:15[/TD]
[TD="class: xl93, width: 40, bgcolor: transparent"]11:15[/TD]
[TD="class: xl101, bgcolor: transparent"]1:00[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl80, bgcolor: transparent"]06/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 92, bgcolor: transparent"] [/TD]
[TD="class: xl90, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 96, bgcolor: transparent"]SITE COMMS[/TD]
[TD="class: xl85, width: 22, bgcolor: transparent"]E[/TD]
[TD="class: xl82, width: 111, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]S&SSM[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Wed 06 April 2016[/TD]
[TD="class: xl93, width: 40, bgcolor: transparent"]12:00[/TD]
[TD="class: xl93, width: 40, bgcolor: transparent"]15:00[/TD]
[TD="class: xl101, bgcolor: transparent"]3:00[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl80, bgcolor: transparent"]06/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 92, bgcolor: transparent"] [/TD]
[TD="class: xl90, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"]B[/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 96, bgcolor: transparent"]FULL EXERCISE[/TD]
[TD="class: xl85, width: 22, bgcolor: transparent"]E[/TD]
[TD="class: xl82, width: 111, bgcolor: transparent"]FULL EXERCISE[/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"]Yes[/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"]Yes[/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Emma Turrell[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]1[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Wed 06 April 2016[/TD]
[TD="class: xl78, bgcolor: transparent"]14:30[/TD]
[TD="class: xl78, bgcolor: transparent"]16:30[/TD]
[TD="class: xl101, bgcolor: transparent"]2:00[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl86, bgcolor: transparent"] [/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl88, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"]E[/TD]
[TD="class: xl81, bgcolor: transparent"]D[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"]A[/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 96, bgcolor: transparent"]DRILL[/TD]
[TD="class: xl82, width: 22, bgcolor: transparent"]D[/TD]
[TD="class: xl82, width: 111, bgcolor: transparent"]COACHED DRILL[/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Jim Burns[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77"]3[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl100, bgcolor: transparent"]Thu 07 April 2016[/TD]
[TD="class: xl78, bgcolor: transparent"]08:30[/TD]
[TD="class: xl78, bgcolor: transparent"]15:30[/TD]
[TD="class: xl101, bgcolor: transparent"]7:00[/TD]
[TD="class: xl102, bgcolor: transparent"]81[/TD]
[TD="class: xl86, bgcolor: transparent"]07/04/16[/TD]
[TD="class: xl84, bgcolor: transparent"]CENTRAL[/TD]
[TD="class: xl81, bgcolor: transparent"]CENTRAL_DE[/TD]
[TD="class: xl82, width: 86, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl88, bgcolor: transparent"]Y[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 19, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 96, bgcolor: transparent"]Coached Exercise[/TD]
[TD="class: xl82, width: 22, bgcolor: transparent"]E[/TD]
[TD="class: xl82, width: 111, bgcolor: transparent"] [/TD]
[TD="class: xl85, width: 176, bgcolor: transparent"]Exercise / Brief[/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 47, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 33, bgcolor: transparent"] [/TD]
[TD="class: xl83, width: 104, bgcolor: transparent"]Mike McMinn[/TD]
[TD="class: xl99, width: 111, bgcolor: transparent"]RP[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl82, width: 391, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

What we need is for the sheet to report back on how many (if any) clashes there are of FT and How many of POL in a message box and then we need these to be highlighted so we can identify the areas and make other arrangements. the code we thought was working was:

Code: 
Dim rowcnt, fndsep, brk1, lentxt As Long
Dim timtxt, strttim, fintim, timefix, DayorNght As String
Dim clash1, clash2, SFRclash, CNCClash, cntSFRclash, cntCNCclash, cntDAYSsfr, cntNIGHTSsfr, cntDAYScnc, cntNIGHTScnc As Integer
Dim loop1, loop2, loop3, cstart, cend As Integer
Dim valdayornight As Double
 
Sub clash()
 clash1 = 0: clash2 = 0: SFRclash = 0: CNCClash = 0: cstart = 0: cend = 0: cntSFRclash = 0: cntCNCclash = 0: cntNIGHTSsfr = 0: cntNIGHTScnc = 0
 
 'Range("A1").Select
rowcnt = Range("c65356").End(xlUp).Row
Application.ScreenUpdating = False
'To ensure each row has data in a 1 is placed in "AS" : "AT" & "AU" are cleared
For loop3 = rowcnt To 2 Step -1
Cells(loop3, "AU") = "1"
Cells(loop3, "AS") = "": Cells(loop3, "AT") = ""
' Display day or night in "G" using start time in "D"
DayorNight = (Cells(loop3, "D").Text)
valdaynight = Val(DayorNight)
If valdaynight < 6 Or valdaynight > 18 Then Cells(loop3, "G") = "Nights"
If valdaynight >= 6 And valdaynight <= 18 Then Cells(loop3, "G") = "Days"
If (Cells(loop3, "D").Text) = "" Then Cells(loop3, "G") = ""
Next loop3
 
'Start the clash checks
 For loop1 = 2 To rowcnt
 '
If Cells(loop1, "C") = Cells(loop1 + 1, "C") And clash2 < 1 Then clash1 = 1
If clash1 = 1 Then cstart = loop1: clash1 = clash1 + 1: clash2 = 1
If clash2 = 1 And Cells(loop1 + 1, "C") <> Cells(loop1 + 2, "C") Then cend = loop1 + 1: clash2 = clash2 + 1: clash1 = 0: clash2 = 0
'SF&R Clash based on SF&R Pump attendance check "AF" Clash shown in "AS"
If cend <> 0 Then
    SFRclash = 0: CNCClash = 0:
    For loop2 = cstart To cend
        If Cells(loop2, "AF") = "Yes" Or Cells(loop2, "AG") = "Yes" Then
        SFRclash = SFRclash + 1
        If Cells(loop2, "G") = "Days" Then cntDAYSsfr = cntDAYSsfr + 1
        If Cells(loop2, "G") = "Nights" Then cntNIGHTSsfr = cntNIGHTSsfr + 1
        End If
        
        If Cells(loop2, "AI") = "Yes" Then
        CNCClash = CNCClash + 1
          If Cells(loop2, "G") = "Days" Then cntDAYScnc = cntDAYScnc + 1
          If Cells(loop2, "G") = "Nights" Then cntNIGHTScnc = cntNIGHTScnc + 1
        End If
    Next loop2
    
If SFRclash > 1 And DateValue(Now) < Cells(loop2, "C") Then
 For loop2 = cstart To cend
        If cntDAYSsfr > 1 And Cells(loop2, "G") = "Days" And Cells(loop2, "AF") <> "" Or Cells(loop2, "AG") <> "" Then Cells(loop2, "AS") = " SF&R clash":
        If cntNIGHTSsfr > 1 And Cells(loop2, "G") = "Nights" And Cells(loop2, "AF") <> "" Or Cells(loop2, "AG") <> "" Then Cells(loop2, "AS") = " SF&R clash":
 Next loop2
 End If
 
 If CNCClash > 1 And DateValue(Now) < Cells(loop2, "C") Then
 For loop2 = cstart To cend
   If cntDAYScnc > 1 And Cells(loop2, "G") = "Days" And Cells(loop2, "AI") <> "" Then Cells(loop2, "AT") = "CNC clash":
   If cntNIGHTScnc > 1 And Cells(loop2, "G") = "Nights" And Cells(loop2, "AI") <> "" Then Cells(loop2, "AT") = "CNC clash":
 Next loop2
 End If
  
  cntDAYSsfr = 0: cntNIGHTSsfr = 0: cntDAYScnc = 0: cntNIGHTScnc = 0
End If
Next loop1
Application.ScreenUpdating = True
cntSFRclash = Range("AS:AS").Cells.SpecialCells(xlCellTypeConstants).Count
cntCNCclash = Range("AT:AT").Cells.SpecialCells(xlCellTypeConstants).Count
If cntCNCclash > 1 Or cntSFRclash > 1 Then
MsgBox "Possible " & cntSFRclash - 1 & " SF&R Clashes or " & cntCNCclash - 1 & " CNC clashes exists." & vbNewLine & "Please make alternative arrangments"
End If
'If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
'    ActiveSheet.ShowAllData
'End If
End Sub
I've wracked my brain for days trying to sort this but I'm building on someone else's work. if there is a simpler or neater way I'd love to adopt it.
I hope I've put this on the forum right and thanks for any help you can provide.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Apologies I only just realised I wasn't very clear. so this is sheet expands and becomes a year long plan upwards of 3000 entries with many things going on, on the same day by many different areas we have a day shift and a night shift on both the FT and the POL and they will assist only one area per shift so we can get 2 per 24hrs from each .
The reason for several Columns of each is because each represents a different way that they could respond be it in person or over the phone etc. however, as I say they will only respond once per shift in any form.
So if I have a yes in any of the FT columns I need the sheet to check if there is another in FT for that Date and shift (Days or Nights) anywhere else in the sheet. and the same for POL and then report that to the user.
The code above was supposed to auto fill the Nights of Days column (Which now says 81 and 82 due to experimentation but would normally say days from 06:00-18:00 and nights for the remainder) but it was messing up and we found that it wasn't capturing all the clashes either. any other help I can provide I'll be happy to.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
983
AlanY
A
S
  • Solved
Tranpose with Editing
Replies
7
Views
1K
Rick Rothstein
Rick Rothstein

Forum statistics

Threads
1,222,907
Messages
6,168,963
Members
452,228
Latest member
just4jeffrey

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top