Do it for "n" cells

K

Kelson

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
(e-mail address removed)
(e-mail address removed)
 
J

Joel

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
 
K

Kelson

Well

Is not exactly like that. I mean, i would like to lock/unlock cells based on
value selected in another cell.
Selected value in Ex
Range Gx:Kx

E9,E10,E11,E12 and all subsequent cells

Any help?

Cheers




"Joel" escreveu:
 
J

Joel

Let me explain how worksheet change works. the target parameter can be one
or many cells. If you change one cell then target is only one cell. If you
do a copy and paste of more than one cell on the worksheet then Target is
many cells. the statement "for each cell in Target" allows you do check all
cells when a copy and paste is performed.

From cell (without the s at the end) you can get the row number or column
number (cell.row and cell.column). if you want to check another cell in the
same row as cell you would use

if cells(.cell.row,"D") = 'this value"
Notice there is a cell with and without the 's'.

I don't completly understnad what you want but I made some changes to the
code as an example

Private Sub Worksheet_Change(ByVal Target As Range)

for each cell in Target
If cell.column = 1 Then
ActiveSheet.Unprotect ("")
if cells(cell.row,"G").locked = true then <= NEW
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
end if
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 said:
Well

Is not exactly like that. I mean, i would like to lock/unlock cells based on
value selected in another cell.
Selected value in Ex
Range Gx:Kx

E9,E10,E11,E12 and all subsequent cells

Any help?

Cheers




"Joel" escreveu:
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
Click to expand...
Click to expand...
 
D

Dave Peterson

Check your other thread.
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
(e-mail address removed)
(e-mail address removed)
Click to expand...
 
K

Kelson

Hi Joel

I got it working!
Have a look on it:


Private Sub Worksheet_Change(ByVal Target As Range)

n = Target.Cells.Row

If Range("E" & n).Value = "A Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G" & n & ":K" & n).Locked = True
ActiveSheet.Range("F" & n).Locked = False
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G" & n & ":K" & n).Locked = False
ActiveSheet.Range("F" & n).Locked = True
ActiveSheet.Protect ("")
End If

End Sub



Thank you anyway!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Repeat code to "n" cells 1
Excel locking cell based on value in another cell 1
Excel Locking cells based on value in another cell 2
Changing Cells depending on data entered in other cells 1
Lock Cells based on Data in another cell 2
Locking specific cells 1
Combo box 1
Is there a way to Calculate with a VBA? 2

Top