Lock/Unlock some cells in a row

Hello all

I am working on a database that is populated by data validation dependent lists. as it will be used by several people I would like to be able to lock each row when all the data has been selected from the drop down boxes.
I am using this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Count > 1 Or Target.Column <> 9 Then Exit Sub
    ActiveSheet.Unprotect
    If Target.Text = "Yes" Then Target.EntireRow.Locked = True
    ActiveSheet.Protect
End Sub

I am using a drop down list in the last column populated with yes and no, the problem is that the whole row is locked when yes selected and therfore it cannot be simply unlocked if someone has made a mistake.

I only need the columns "B to H" Locked/Unlocked in the row when yes/no is selected in "I"

Can anybody help me with the alterations to this code.

Thanks in advance

Hi Leith

Thank you for answering so quickly and providing an answer, I have tried your code and it works perfectly when locking.....but can it be written that when "Yes" is in column "I" the corresponding row is locked and when "No" is entered the row is unlocked?

Thanks for your help so far

Processchip

Hi Leith

I did a bit of research and had a go myself and came up with this..

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count > 1 Or Target.Column <> 9 Then Exit Sub
    ActiveSheet.Unprotect
      If Target.Text = "No" Then
         Target.Offset(0, -7).Resize(1, 7).Locked = False
         Else
      If Target.Text = "Yes" Then
         Target.Offset(0, -7).Resize(1, 7).Locked = True
      ActiveSheet.Protect
      End If
     End If
End Sub

It works well and can not see any problems but maybe you could just veryfy it

Anyway thanks again for your help

Processchip

OK I'm getting brave now....

I added an extra bit of code

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count > 1 Or Target.Column <> 9 Then Exit Sub
    ActiveSheet.Unprotect
      If Target.Text = "No" Then
         Target.Offset(0, -7).Resize(1, 7).Locked = False
         Else
      If Target.Text = "Yes" Then
         Target.Offset(0, -7).Resize(1, 7).Locked = True
      ActiveSheet.Protect

Const triggerColumn = "I"

If Target.Column <> Range(triggerColumn & 1).Column _
Or Target.Cells.Count > 1 Then
Exit Sub
End If

Target.Offset(1, -8).Activate
      End If
     End If
End Sub

The code sends to to the start of the next row when the last cell in the previous row has been populated, the only thing is is that it doesnt work like pressing the TAB button as the row is blank with no colour, formatting and dropdown boxes. I can manage without it to be honest but if it is a simple job to can anyone help.

Processchip