Worksheet change functions should look something like this. This is only an
example. code may not do exactly what you need it to do.
Private Sub Worksheet_Change(ByVal Target As Range)
for each cell in Target
If cell.column = 1 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
Else
If cell.column = 2 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
end if
End If
next cell
End Sub
"Kelson" wrote:
Hi all
My code reads one cell and through the options it locks or not others cells.
Im trying to extend my programming to all subsequent cells. How can I do it?
Like using "for" instruction?!?! Does anybody help me?
Private Sub Worksheet_Change(ByVal Target As Range)
If [E9]= "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
ElseIf Target.Cells.Text = "B" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
End If
End Sub
Thanks in advance
Kelson