locking cells automatically

could someone tell me how to lock cells automatically in excel.

i have a date field in column a and i want the code to look at the cells in column a and lock the whole row if that cell is greater that 1 week old.

i dont use a password in the protect sheet but i just want to limit accidental deletions or alterations.


thanks

If you lock a cell without protecting the sheet then it won't do anything, although you can protect the sheet without a password.

You might be able to achieve this with some sort of data validation if you don't protect the sheet.

Well the code would be something like:

for count = 2 to Range("A" & rows.count).end(xlup).row
  if range("A" & count) < date - 7 then Range("A" & count).entirerow.locked = true
next

You would need to assign it to an event, it could be when you open the workbook, when you change a cell etc. I would suggest that running it everytime the workbook opens is probably ok? For that you need:

private sub workbook_open()
for count = 2 to Range("A" & rows.count).end(xlup).row
  if range("A" & count) < date - 7 then Range("A" & count).entirerow.locked = true
next
end sub

saved in the "thisworkbook" object module.

i cant seem to get that to work. could you upload a sample please

Hi,

Sorry uploads are not working for me at the moment, I had a look and made some changes to the code:

Private Sub workbook_open()
With Sheets("Sheet1")
    .Unprotect
    For Count = 2 To Range("A" & Rows.Count).End(xlUp).Row
      If .Range("A" & Count) < Date - 7 Then .Range("A" & Count).EntireRow.Locked = True Else .Range("A" & Count).EntireRow.Locked = False
      Next
    .Protect
End With
End Sub

Change the sheetname as neccessary.

where am i adding this as i tried on sheet1 , tried on thisworkbook and tried as a module and then saved it, but i can still edit the rows where there is a date older that today. sorry to be a pain in the ..... lol

thisworkbook.

It will run when the workbook opens - you can press f5/click run in the macro manually to test though. Maybe if you try uploading your workbook with the macro as you are trying to use it I can have a look.

i think i have managed to do it now. thank you

ive realised that after i marked this as solved it doesnt work on the values that is part of a table. it works on the values that isnt in a table so is there anything different i need to do.