Sheet2に以下のように入力してあるとします。
2行目にはWeekdayで返される値を入力するものとします。
月曜日は2、火曜日は3、水曜日は4・・・といった感じです。

	A	B	C	D
1	月曜	火曜	水曜	木曜
2	2	3	4	5
3	出勤	出勤	休日	休日


カレンダーはSheet1のB4以降に日付データが入力してあるとします。

Sub test()
Dim i As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

    With sh1
     .Range(.Cells(4, 3), .Cells(35, 3)).Value = ""
        For i = 4 To 35
            If .Cells(i, 2).Value = "" Then Exit For
            If Weekday(.Cells(i, 2).Value) = sh2.Cells(2, 1).Value Then Cells(i, 3).Value = sh2.Cells(3, 1).Value
            If Weekday(.Cells(i, 2).Value) = sh2.Cells(2, 2).Value Then Cells(i, 3).Value = sh2.Cells(3, 2).Value
            If Weekday(.Cells(i, 2).Value) = sh2.Cells(2, 3).Value Then Cells(i, 3).Value = sh2.Cells(3, 3).Value
            If Weekday(.Cells(i, 2).Value) = sh2.Cells(2, 4).Value Then Cells(i, 3).Value = sh2.Cells(3, 4).Value
        
        Next i
    End With
End Sub