On Apr 5, 12:34*am, johnsail
wrote:
Hi
Have a worksheet of 40 rows and cols A to K.
Only cols B,C and D are unlocked.
Data is entered along each row.
Within each row
a) If the data entered in cell D = "999" then cell in col E should be
unlocked.
b) If data < "999" then cell in col J should be unlocked.
c) If D is changed from "999" to something else then contents of E need to
be deleted and the cell locked.
d) If D is changed to "999" from any other value the contents of cell in col
J need to be deleted and replaced with a formula and then locked.(The formula
is held in a hidden cell K on each row).
e) If the contents of cell D are deleted then both (c) and (d) above need to
be actioned.

Is this possible in VB?

Regards
John

Hi John,

This may not be perfect depending on exactly how you want your
criteria to behave, but it should give you a starting point:

'This is necessary to avoid having to unprotect
'and protect the sheet every time the macro fires
Private Sub Worksheet_Activate()
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OldVal, CaseE As Boolean

If Target.Column = 4 Then
OldVal = Target.Offset(0, 100).Value
CaseE = IsEmpty(Target)
If Target = 999 Then
Target.Offset(0, 1).Locked = False
Else
Target.Offset(0, 6).Locked = False
End If
If (OldVal = 999 And Target < 999) Or CaseE Then
With Target.Offset(0, 1)
.ClearContents
.Locked = True
End With
End If
If Target < OldVal And Target = 999 Or CaseE Then
With Target.Offset(0, 6)
.Formula = Target.Offset(0, 7).Formula
.Locked = True
End With
End If
Target.Offset(0, 100) = Target.Value
End If
End Sub

It should just be a case of massaging the criteria and their order
until you get what you want. Note that I did have to use a 'helper'
cell at Column CZ to hold the 'old value' of the cell in column D,
since the worksheet doesn't have a 'before change' event. You can
certainly move this closer to your data... I was just playing it safe
by offsetting by 100.

Cheers,
Ivan.