PDA

View Full Version : Lock Cells based on criteria



amandeep08
08-09-2016, 08:51 AM
I want to lock cells if the date given in S column is less than the date mentioned in the Z1 Cell (IN OTHER SHEET) in same file.

Below are the issues I am facing:

1. How to pick cell from Z1 cell from other sheet
2. How to make a range of cells locked instead of complete row
3. If some cell in S Column is more than the date mentioned in Z1 or blank, it will allow me to modify the data

Below is the query written:

Private Sub Workbook_Open()

lastrow = Sheets("Pre Procured").Cells(Rows.Count, "s").End(xlUp).Row
Worksheets("Pre Procured").Unprotect
Dim j As Long

For j = 2 To lastrow
If Sheets("Pre Procured").Cells(j, "s").Value < Range("XFD1146").Value Then
Rows(j).Locked = 1
Else
Rows(j).Locked = 0
End If
Next j

Worksheets("Pre Procured").Protect UserInterfaceOnly:=True
End Sub

mrmmickle1
08-09-2016, 06:21 PM
This is cross posted on MrExcel.