Who wants to work weekends in Excel

Let’s take the frustration out of user forms

Become an Affiliate and earn 25% on Course Sales

For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.

Contact me regarding customizing this template for your needs.

Click for online Excel Consulting

I am able to provide online help on your computer at a reasonable rate.

I use a Blue condenser Microphone to record my videos, here is the link

Check out Crowdcast for creating your webinars

If you need to buy Office 2019 follow

I use Tube Buddy to help promote my videos
Check them out

Follow me on Facebook

TWEET THIS VIDEO

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.

templates with code available for $50 USD

code
Sub filleachday()
‘ I am declaring each of the values
Dim r, c As Long
‘ r for row, c for colum in week
Dim ra, ca As Long
‘ ra row and ca column in availability
lastrow = 18
‘looking for the last row in the Staff list counting from the bottom up

c = 2

For c = 2 To 5
r = 5
ra = 6
‘this is telling it where each day matches with availability
Select Case c
Case Is = 2
ca = 9
Case Is = 3
ca = 11
Case Is = 4
ca = 13
Case Is = 5
ca = 15

End Select
For ra = 6 To lastrow

‘saturday
If Cells(ra, ca).Interior.ColorIndex = xlColorIndexNone Then
If ActiveSheet.Cells(ra, ca).Value = “y” Then
ActiveSheet.Cells(ra, ca).Interior.ColorIndex = 34
txtbox = ActiveSheet.Cells(ra, 8).Value
ActiveSheet.Cells(r, c).Value = ActiveSheet.Cells(ra, 8).Value

GoTo row6
End If
End If
Next ra
row6:
‘Saturday
‘second time slot
r = 6
For ra = 6 To lastrow
‘it is check whether each cell has colour if it does not it will move on
If Cells(ra, ca).Interior.ColorIndex = xlColorIndexNone Then
If ActiveSheet.Cells(ra, ca).Value = “y” Then
‘it is searching for a cell in this day with a y
‘if it finds it will change the colour and move it to the day of the week
ActiveSheet.Cells(ra, ca).Interior.ColorIndex = 34
ActiveSheet.Cells(r, c).Value = ActiveSheet.Cells(ra, 8).Value

GoTo row8
End If
End If
Next ra
‘Sunday
row8:
r = 8
ca = ca + 1
For ra = 6 To lastrow
If Cells(ra, ca).Interior.ColorIndex = xlColorIndexNone Then
If ActiveSheet.Cells(ra, ca).Value = “y” Then
ActiveSheet.Cells(ra, ca).Interior.ColorIndex = 34
ActiveSheet.Cells(r, c).Value = ActiveSheet.Cells(ra, 8).Value
‘
GoTo row10
End If
End If
Next ra

‘Sunday
‘second time slot
row10:
r = 9

For ra = 6 To lastrow
If Cells(ra, ca).Interior.ColorIndex = xlColorIndexNone Then
If ActiveSheet.Cells(ra, ca).Value = “y” Then
ActiveSheet.Cells(ra, ca).Interior.ColorIndex = 34
ActiveSheet.Cells(r, c).Value = ActiveSheet.Cells(ra, 8).Value

GoTo ende
End If
End If
Next ra
ende:
Next c

End Sub

Watch more new videos about Excel Office | Synthesized by Mindovermetal English