Dear Mr Ryan,

Your code has protected all cells
i changed your code a little bit as below
' b5 is date, i want if b5 is in b7:b1000 then it must allow change in
' g7:j1000, else should not allow, as the dates could be prior to date in b5
' which means i like to protect data entered prior to date in b5
' suppose $b$5 has 27-01-2010 then when
' b12 has 26-01-2010 - dont allow to change in g12:j12 but
' b13 has 27-01-2010 - so allow change in g13:j13

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
With Sheets("Biz FT Daily")
' scan each cell in range to test if it = 1
For Each rng In .Range("B7:B1000")
If rng.Value = Range("b5") Then
..Unprotect
Else
..Protect
Exit Sub
End If
Next rng

' lock cells
.Range("G7:J1000").Locked = True

' protect sheet so Col. F is locked
..Protect
End With

End Sub

pl help me out, thanks in advance.


"Ryan H" wrote:

Put this macro in the sheet module. This macro will scan ech cell in range
C5:C1000 looking for values not equal to 1. If it finds a value other than 1
it unprotects the worksheet.

In order to lock cells you have to highlight the cells you wish to lock,
right click the range, then under the protection tab, click the Lock
checkbox. The cells will not become locked untill you protect the sheet.

This code protects the sheet and unprotects the sheets according to values
in C5:C1000. So make sure range F5:F1000 is set to be locked when the sheet
is protected. And unlock cells that you want the users to enter data in.
Hope this helps! If so, let me know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

With Sheets("Sheet1")

' scan each cell in range to test if it = 1
For Each rng In .Range("C5:C1000")
If rng.Value < 1 Then
.Unprotect
Exit Sub
End If
Next rng

' lock cells
.Range("F5:F1000").Locked = True

' protect sheet so Col. F is locked
.Protect
End With

End Sub
--
Cheers,
Ryan


"Eddy Stan" wrote:

HI ALL,

' at start all cells in range c5:c1000 will be 0
' wish to lock cell f5:f1000, when c5:c1000 is 1
' wish if f5:f1000 access itself is not possible when c5:c1000 is 1


Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("c5:c1000")

If Target.cell(0, 3) = 1 Then

Target.Cells.Offset(0, 3).Locked = True

Else

Target.Cells.Offset(0, 3).Locked = False

End If

End Sub

PL HELP