Unable to lock the cell based on current date

[dd08]

Hi experts, I am bit new to VBA macros .
I have made one daily attendance sheet, where in entered value to be locked once current date is passed. Values are getting selected from drop down.

code i have used


Private Sub worksheet_Change(ByVal Target As Range)



Dim col As Range



With ThisWorkbook.Sheets("Sheet1")

.Unprotect "123"

For Each col In .UsedRange.Columns

col.EntireColumn.Locked = col.Range("C7").Value < Date

Next col

.Protect "123"

.EnableSelection = xlNoRestrictions

End With

End Sub


My requirement- user will select status on daily basis and once day is over user wont be able to change entered status.

 

[AC PORTA VIA]

Will this work for you

Private Sub worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim row As Range
Dim cell As Range
Set rng = Range("C7:R7")
With ThisWorkbook.Sheets("Sheet1")

For Each row In rng.Rows
.Unprotect "123"
  For Each cell In row.Cells
   If cell.Value < Date Then
ActiveSheet.Columns(cell.Column).Locked = True
   End If
  Next cell

Next row

.Protect "123"

End With
End Sub