Hello,
I am currently attempting to make a calendar function for a manager in my office. She would like it so that when when staff input their time off that is formats a overall calendar view to show when they are off. with each member of staff down the left and the dates shown across the top.
I had thought to loop through all the names on the employee tracker list to filter and amend the cells but I am having trouble making it work, I will post what I have been trying below. I apologise for the mess, i am still getting to grips with VBA. On the "2017" sheet I have put the dates adjacent to each person.
Code
Sub Edit2017()
Dim rng As Range
Dim c As Range
Dim x As Range
Dim y As Range
Dim myrow As Range
Dim a As Range
Dim l As Long
Application.ScreenUpdating = False
With Worksheets("employee leave tracker")
l = .Cells(.Rows.Count, 1).End(xlUp).Row
With .Range("B3")
'On Error Resume Next
For Each c In .Offset(1).Resize(l).SpecialCells(xlCellTypeVisible)
With Worksheets("2017").Range("A5")
x = .Range.Cells(.Rows.Count, 1).End(xlUp).Row
.AutoFilter , field:=1, Criteria1:=c.Value
If x.Cells.Value = c.Offset(0, 1) Then .Cells.Interior.ColorIndex = 37
Worksheets("2017").AutoFilterMode = False
End With
Next c
End With
End With
Application.ScreenUpdating = True
End Sub]Any help would be appreciated.