how to lock/unlock range of cells with a yes/no dropdown menu....STAT!!

[scockster]

I've seen just about every form of this except the one I need for my sheet specifically.
In Row H starting at H5 I have a drop down menu with a Y/N option and it goes all the way down to around H950. I want to lock the remaining cells in each row seperately if N is selected. In my case if I selected N in H5 then it would lock I5 thru DJ5. If Y is selected then it keeps them unlocked or unlocks them just in case N is selected on accident. I want this type of macro of event to occur seperately for each row. For example:
H5=N then lock I5 thru DJ5
H6=N then lock I6 thru DJ6
H7=Y then keep I7 thru DJ7 unlock or unlock them if N was selected first on accident
H8=N then lock I8 thru DJ8
In addition I would like to color the cells in black which I could do later with conditional fomatting I guess. Someone please help.
I would hate to have to write this macro of line manually 950 times.
Thanks for any help on this!!!

 

[JoeMo]

I'll assume you know how to install an event macro and that your sheet is protected. Substitute your password for the bit in red font.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("H5:H950")
If Not Intersect(Target, R) Is Nothing Then
    Me.Protect Password:="put your password between the quote marks", userinterfaceonly:=True
    For Each c In Intersect(Target, R)
        If c.Value = "N" Then
            c.Offset(0, 1).Resize(1, Columns("DJ").Column - Columns("H").Column).Locked = True
        ElseIf c.Value = "Y" Then
            c.Offset(0, 1).Resize(1, Columns("DJ").Column - Columns("H").Column).Locked = False
        End If
    Next c
End If
End Sub

 

[scockster]

I'm assuming just pasting it in my VBA Project is what you meant by "installing" which I did and it works great. The only thing missing is that I wanted the cells to turn black as well once locked. Can you help me with that? Here is what I had at first.(BELOW) I also wanted to show you that adding a message when selecting Y or N would be a nice addition as well. I hope I have that correct. Finally, would Userinterfaceonly help in this situation like I had initially in my code below?

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H5") = "Y" Then
Range("I5:DJ5").Locked = False
MsgBox "YOU OWN 15 MORE DOLLARS *****!",vbInformation, "PAY UP"
ElseIf Range("H5") = "N" Then
Range("I5:DJ5").Locked = True
MsgBox "BETTER LUCK NEXT YEAR", vbInformation,"lOSER!!"
End If
Sheets("HUMANTEAMS").ProtectPassword:="123456", userinterfaceonly:=True
End Sub

 

[scockster]

Sorry, I see that Userinterfaceonly is in the code you sent me. Thank you.

 

[scockster]

Well Dang. I know I'm probably pushing the envelope at this point but this site has been so much help. Here is my issue with the event macro. I want to start it again when you get to Row N. For example this is what I thought would work but is doesn't. Can you put a switch in or merge them somehow. I would do it 6 times total on this sheet. I would have the Y/N drop downs on ROWS H (which you have solved), N, T, Z, AF, and AL. I thought the first 2 would look something like this but it doesn't work.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("H5:H950")
If Not Intersect(Target, R) Is Nothing Then
Me.Protect Password:="123456", userinterfaceonly:=True
For Each c In Intersect(Target, R)
If c.Value = "N" Then
c.Offset(0, 1).Resize(1, Columns("DJ").Column - Columns("H").Column).Locked = True
MsgBox "BETTER LUCK NEXT YEAR", vbInformation, "LOSER!!"
ElseIf c.Value = "Y" Then
c.Offset(0, 1).Resize(1, Columns("DJ").Column - Columns("H").Column).Locked = False
MsgBox "YOU OWN 15 MORE DOLLARS", vbInformation, "PAY UP"
End If
Next c
End If
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("N5:N950")
If Not Intersect(Target, R) Is Nothing Then
Me.Protect Password:="123456", userinterfaceonly:=True
For Each c In Intersect(Target, R)
If c.Value = "N" Then
c.Offset(0, 1).Resize(1, Columns("DJ").Column - Columns("N").Column).Locked = True
MsgBox "BETTER LUCK NEXT YEAR", vbInformation, "LOSER!!"
ElseIf c.Value = "Y" Then
c.Offset(0, 1).Resize(1, Columns("DJ").Column - Columns("N").Column).Locked = False
MsgBox "YOU OWN 15 MORE DOLLARS", vbInformation, "PAY UP"
End If
Next c
End If
End Sub

AGAIN, THANK YOU SO MUCH FOR YOUR HELP!!!
[/FONT]