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.
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.