This is used for a scheduling tool that is in a workbook, the workbook has an sheet called IW49N Download which is a data dump from a program called SAP, it then has 7 other sheets 1 for each day of the week. Below I am explaining a problem I have with the sheets for each day of the week.

I have a list of names in 1 column. In front of each name is a row of 24 cells. Each cell represents an hour of the day. The 24 cells are conditionally formatted to be grey when a name is unavailable for a specific day or set of hours in that day. The conditionally formatted cells would not copy and paste so you'll have to imagine 24 cells after Name1, Name2, Name3 etc. The conditional formatting is based on the 3 columns of numbers that are after the 24 conditionally formatted cells you cannot see. For Name1 the 7 means the employee starts at 7AM, the 8 means the employee works for 8 hours and the 15 means the employee ends his shift at 3PM. Likewise for Name3 the employee starts at 11PM works for 8 hours and is done at 7AM, Name6 the employee starts the shift at 3PM, works 8hours and is finished at 11PM.

Name1 7 8 15
Name2 7 8 15
Name3 23 8 7
Name4 7 8 15
Name5 7 8 15
Name6 15 8 23

The values in the 3 columns are found by the following function/code:
Column 1 (start time of shift) =IF(ISNA(VLOOKUP($C146,'Schedule WK 46.xlsm'!Schedule,11,FALSE)),0,VLOOKUP($C146,'Schedule WK 46.xlsm'!Schedule,11,FALSE))
Column 2 (duration of shift) =IF(ISNA(VLOOKUP($C146,'Schedule WK 46.xlsm'!Schedule,10,FALSE)),0,VLOOKUP($C146,'Schedule WK 46.xlsm'!Schedule,10,FALSE))
Column 3 (End of shift) =IF(AK146+AL146>24,AK146+AL146-24,AK146+AL146) This function converts the end time from a 12hour clock to a 24hour clock time
The array named schedule is found on a separate tab and the data in that tab is downloaded from another program.

The conditional formatting of the 24 cells after the names looks like this

Formula: =$AL146=0 Applies to =$L$146:$AI$170 Makes all 24 cells grey based on duration of shift, if shift is zero all cells are grey
=$AK146=23 Applies to =$L$146:$AA$170 If start of shift is 11PM or 2300hrs then make the first 16 cells grey
=$AK146=15 Applies to =$AB$146:$AI$170,$L$146:$S$170 If start of shift is 3PM or 1500hrs then make the first 8 and last 8 cells grey leaving the middle 8 cells white
=$AK146=0 Applies to =$L$146:$AI$170 If start of shift is zero (vlookup cant find employees name from downloaded data) then make all 24 cells grey

The above gives me employee availability throughout the day.

On the same sheet I have 12 columns of importance

Operations Work Center Craftsman WO # E/I # E/I hr total MW # MW hr total PF # PF hr total
Instrument Tech Name1 200171648 0 0 0 0 0 0 0 0 0
Pipefitter Name2 200171648 0 0 0 0 0 0 0 0 0
Millwright Name3 200171648 0 0 0 0 0 0 0 0 0
Instrument Tech Name1 200171647 0 0 0 0 0 0 0 0 0
Pipefitter Name2 200171647 0 0 0 0 0 0 0 0 0
Millwright Name3 200171647 0 0 0 0 0 0 0 0 0

Under the E/I#, MW#, PF# columns I have the following formula
=IF(M12=0,"0",N12/M12)
This is used to calculate the number of Electrician/Instrumentation (E/I) craftsman that I need based on the next 2 columns

Under the E/I hr, MW hr, and PF hr columns I have the following formula
=SUMIFS('IW49N Download'!$I$7:$I$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNE_I")
=SUMIFS('IW49N Download'!$I$7:$I$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNMRWT")
=SUMIFS('IW49N Download'!$I$7:$I$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNPIPE")

Under the column labeled "total" for each craft I have
=SUMIFS('IW49N Download'!$J$7:$J$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNE_I")
=SUMIFS('IW49N Download'!$J$7:$J$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNMRWT")
=SUMIFS('IW49N Download'!$J$7:$J$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNPIPE")

In the above formulas, "IW49N Download" is a sheet that has data dumped into it from another program called SAP which is used to track specific maintenance jobs, equipment and parts charged to each job and the number of hours required for each craft on each job. With that said, the above formulas are only somewhat useful and I'll need to give an example to explain why. Below is 1 line of data that is dumped into the IW49N Download worksheet

Order Oper.Work Center Number Normal duration Total Man Hours
200172497 BNPIPE 3 4.0 12.0

What this says is Work Order Number 200172497 needs 3 Pipefitters (BNPIPE) each for 4 hours for a total of 12 hours
That means on the worksheet for Monday lets say, I need three rows each with the order number 200172497. The reason I need 3 rows of the same work order number is because I need to assign Name1, Name2, Name3 to that job. Since the data comes in like it does above then on the worksheet for the day of the week the formula
=SUMIFS('IW49N Download'!$I$7:$I$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNPIPE")
will only return the number 4. and
=SUMIFS('IW49N Download'!$J$7:$J$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNPIPE")
will only return the number 12, with those 2 numbers entered then
=IF(M12=0,"0",N12/M12)
will return the number 3

The point of the scheduling tool is to be able to schedule Name1 on multiple jobs to fill their 8 hour day. The point of the conditional formatting and what I would like it to be able to do is to conditionally format the 24 cells or 8 cells after their name based on the hours in the "Total" column. So if Name1 appears on the list 2 different times each for 4 hours the 8 cells after his name would be conditionally formatted and shaded in some different color than grey or white. This way I would be able to quickly tell Name1 has his day full and I will not schedule him for any more jobs. I have uploaded a file that has only 1 day of the week on it. I'm sorry for the extremely long winded explanation however I felt it was necessary to explain the problem

Biron Maintenance Scheduling rev22.xlsm