Lock column based on date

Question

I need to lock columns based on days stated in G8, H8, I8, J8, and K8.

I need to lock G9 through G44 if G8 doesn’t have today's day.

If today is Sunday and G8 has Sunday then G9 to G44 should be open If not then the data range should be locked.

This should be done with all the columns from G to K. How can I do that? Thanks a million in advance.

Private Sub worksheet_Change(ByVal Target As Range)
Dim col As Range
    With ThisWorkbook.Sheets("Sheet1")
        .Unprotect "ABCDE"
        For Each col In .UsedRange.Columns
            col.EntireColumn.Locked = col.Range("A1").Value < Date
        Next col
        .Protect "ABCDE"
        .EnableSelection = xlNoRestrictions
    End With
End Sub

What isnt working? Requirement 1 is part of requirement 2, so its just an example? Your code works for me, except for that it only checks for older dates, doesnt lock the date cell itself and once the sheet is locked, its locked and you have to manually unlock it to run this macro. for the last problem, consider running the macro when the workbook is opened/sheet activated as well? Worksheet_Activate and Workbook_Open() — Czeskleba

Justyna MK Justyna MK · Accepted Answer · 2020-02-18T19:02:51

Welcome to SO. Your code looks good, all you need to do is to add a few more If conditions. Give it a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col                 As Long
    Dim tDate               As Date

    tDate = Date 'today's date

    With ThisWorkbook.Sheets("Sheet1")
        '.Unprotect "ABCDE"
        For col = 0 To .Range("G9:K44").Columns.Count - 1
            'check if row 8 doesn't have today's date
            If .Range("G8").Offset(0, col).Value <> tDate Then
                'if today is Sunday and row 8 has Sunday
                If Format(tDate, "dddd") = "Sunday" And Format(.Range("G8").Offset(0, col).Value, "dddd") = "Sunday" Then
                    .Range("G8:G44").Offset(0, col).EntireColumn.Locked = False
                Else
                    .Range("G8:G44").Offset(0, col).EntireColumn.Locked = True
                End If
            End If
        Next col
        '.Protect "ABCDE"
        '.EnableSelection = xlNoRestrictions
    End With
End Sub

Change tDate to some other date to test the code, e.g. tDate = '2020-02-16' (Sunday).

Lock column based on date

1 Answers