Does this help?


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "X$1" Then
With Sheets("mysheet")
.Protect Password:="mypassword"
With .Range("Y1")
'.With code here
End With
End With
End If
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"ozgrid.com" wrote in message
...
What sort of reference is "$y$n" and Range("$y$n")???



--
Regards
Dave Hawley
www.ozgrid.com


"tony" wrote in message
...
Can someone explain the bizzare behaviour I am getting with the
following select statement?

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("mysheet").Select
ActiveSheet.Unprotect ("mypassword")
Select Case Target.Address
Case $x$n
Range("$y$n").Select
Range("$y$n").Locked = False
'perform some action to cell $y$n
Range($y$n").Locked = True
End Select
ActiveSheet.Protect("mypassword")
End Sub

You would expect that the sub would unprotect the worksheet, select
the cell, unlock the cell, perform the action ,lock the cell and
protect the worksheet.

What appears to happen is this:
The sub unprotects the worksheet, selects the cell, unlocks the cell,
performs the action , protects the worksheet and then attempts to lock
the cell (which it can't because the worksheet is now protected).

I've managed to work around this by putting the protect bit in the
case statement but surely this isn't right is it?

Just in case your wondering I'm not referencing merged cells - I
thought that was the problem but is isn't.