Lock range after 24 hrs

[blacklabel]

I have an employee stat sheet that I created for my department. It's set up that each row is a specific day. The days start at B4 and ends at AG37. This would be an example of the June 2008 stat sheet. What I'm trying to do is automatically lock the previous data when a new day starts so nothing can be altered without a password that only I and their manager will have. Let me give you an example:

B4:AG4 = June 1st
B5:AG5 = June 2nd
B6:AG6 = June 3rd

For an example let's say today is June 3rd. I need rows B4:AG5 to be locked. But on June 4th, I need B4:AG6 to be locked. I'm trying to automate this so I don't have to change it every day.

Is this possible?

Thanks in advance.

 

[DonkeyOte]

is the date visible anywhere ?

If it were you could simply highlight the sheet (dates excepted) and unlock the cells.

Enter some data validation across the range -- eg assuming date in A you could enter the following formula as custom validation

=$A1>=TODAY()

copy the validation across all cells you want to potentially lock down

then protect the sheet.

the end user could not then impact the historic data unless you were to unprotect the sheet and clear the validation from that cell beforehand.

Obviously if you want a VB solution that's fine but please specify as to where the date can be located.

 

[blacklabel]

Thank you for your reply. The date is visable and located in Column A. A VB solution is preferred but I really didn't think of validating the way that you suggested.

 

[blacklabel]

Actually this is not what I'm looking to do. It's getting there though. If they try to change data in a row which has a previous date than today, then I need a pop up of some type to key in a password to do so. If they do not know the password, the changes do not save.

I hope that makes sense.

 

[blacklabel]

I did find this code:

Sub Macro1()
Dim intLastRow As Integer, intRow As Integer
Application.ScreenUpdating = False
intLastRow = ActiveSheet.UsedRange.Rows.Count
For intRow = 2 To intLastRow
If Cells(intRow, 1) = Date Then
ActiveSheet.Unprotect "test"
Rows(1 & ":" & intRow - 1).Locked = True
Rows(intRow).Locked = False
Rows(intRow + 1 & ":" & intLastRow).Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, PASSWORD:="test"
Exit For
End If
Next intRow
Application.ScreenUpdating = True
End Sub

But it doesn't seem to be working for me. I wondered if I should protect the sheet as a test with the same password in the code but that didn't work for me either. When I protect the sheet it locks all rows. When I don't protect the sheet and put the code in it doesn't lock anything.

 

[blacklabel]

Anyone have any thought on this? I'm still not able to get this working. I need a macro to auto start when the workbook is opened. If Column A does not have today's date, then lock everything. If Column A has today's date, then just unlock that row.