I'm very new to VBA and am struggling to find a solution to this. I have a column of equipment and rows of dates to act as a checkout system. The idea is a person would add their name to the dates and the date along with the name shifts 1 cell left daily so that column B is always the current day. Currently, the code I have works perfectly to achieve this but I am wanting to add the functionality of including any cell fill color to also shift 1 cell left every day. Would someone have any suggestions what I could add or modify to this existing code to include fill color? If you need any additional information or clarification let me know
VBA Code:
Private Sub Worksheet_Calculate()
' check if first day is changed
If [b8] = [z1] Then Exit Sub
Dim a, i As Long
On Error GoTo CleanExit:
Application.ScreenUpdating = False
Application.EnableEvents = False
' days since last data shift
i = [b8] - [z1]
If i > 0 And i < 7 Then
'store data to shift
a = Range(Cells(10, 1 + (i + 1)), Cells(40, 63)).Value
'clear old data
Range("b10:bk40").ClearContents
'write stored data
Cells(10, 2).Resize(31, 62 - (i)) = a
Else
'clear old data
Range("b10:bk40").ClearContents
End If
' store new date
Range("z1") = Range("b8").Value
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
CleanExit:
Application.ScreenUpdating = True
Application.EnableEvents = True
Err.Clear
End Sub
