Locking entire column once date passes

[benntw]

I have a spreadsheet with dates in R3:CE3 . I was looking to have this auto lock the cells from edit anything two days prior to today's date. Columns A:Q are protected when I protect the sheet. R:CE are the only columns you can edit. Hopefully someone can help me out on this. Appreciate the help.

 

[duggie33]

Hi benntw,

I do not believe there is any way to do this without using VBA. If simply changing the formatting (color/font/borders)of the cells is an option, it can be done with Conditional Formatting.



Doug

 

[benntw]

I was looking to see if anyone had some VBA that would do the trick. I have seen some VBA that pertains to rows , but nothing for columns.

 

[duggie33]

Try this code in the Workbook Open event.

Private Sub Workbook_Open()

[FONT=Calibri][SIZE=3][COLOR=#000000]     Dim rng_DateRange As Range
    Dim cell As Range
    
    Sheet1.Unprotect
    
    Set rng_DateRange = Sheet1.Range("R3:CE3")
    
    For Each cell In rng_DateRange
    
        If cell.Value < Date - 2 Then
            cell.EntireColumn.Locked = True
        End If
    
    Next cell
    
    Sheet1.Protect
    
End Sub[/COLOR][/SIZE][/FONT]

 

[benntw]

Thank you very much. This works perfect.