Timesheet with Highlight Annual Leave and auto populate hours in cells

[ismailshajji]

Hello Friends I would like to thank all of this forum members and especially "protonLeah " in helping me design a timesheet spreadsheet to help my team .I am not sure if it is possible but I would like to have a vb script in which the holiday start to holiday end dates are coloured in any colour ignoring the Weekends as weekends are coloured using conditional formatting, and the columns A.M. IN / A.M. OUT / P.M. IN are populated with the following hours 800/1200/1230/1600 if the holiday is for full day and if it is Am then 800/1200 if its is PM then it should be 1200/1600 again ignoring weekends.

[JeteMc]

As the original post is just over 24 hrs old, it may be too early to start offering conditional formatting alternatives to VBA solutions, but here goes.
1. Change the layout on the annual leave sheet as shown in A8:B13,
2. Populate Q4:S4 with Full, AM and PM respectively.
3. Populate Q5:S using: =IF(WEEKDAY($A5,2)>5,"",INDEX('Annual Leave'!$B$9:$B$13,MATCH($A5,'Annual Leave'!$A$9:$A$13,0))=Q$4)
4. Condition formatting rules:
For Full: =$Q5=TRUE
For AM: =$R5=TRUE
For PM: =$S5=TRUE
Let us know if you have any questions.

[ismailshajji]

As the original post is just over 24 hrs old, it may be too early to start offering conditional formatting alternatives to VBA solutions, but here goes.
1. Change the layout on the annual leave sheet as shown in A8:B13,
2. Populate Q4:S4 with Full, AM and PM respectively.
3. Populate Q5:S using: =IF(WEEKDAY($A5,2)>5,"",INDEX('Annual Leave'!$B$9:$B$13,MATCH($A5,'Annual Leave'!$A$9:$A$13,0))=Q$4)
4. Condition formatting rules:
For Full: =$Q5=TRUE
For AM: =$R5=TRUE
For PM: =$S5=TRUE
Let us know if you have any questions.

thank you so much my friend , I will look into this and let you know .