VBA lock and colour range except active cell

[MartinL]

Can Anyone help with this code:

I am trying to allow only one entry per row
so once it is filled (Col V counts the number of entries)
Bn:Un gets locked and coloured black
the cell just populated remains uncoloured and unlocked

If the cell contents get deleted the whole row is unlocked

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With ActiveCell
    If V & Row = 1 Then _
        RANGE("B" & Row ":U" & Row).Locked = True
        .Interior.Color = Black
     End If
        
     ActiveCell.Interior.Color = White
     ActiveCell.Locked = False
     
     If ActiveCell = "" Then _
         RANGE("B" & Row ":U" & Row).Locked = False
        .Interior.Color = White
     End If


End Sub

the code above does not compile



Martin

 

[SteveO59L]

If the "cell is locked", how does the contents get deleted ?


You need a wend at the finish if the with block

 

[MartinL]

If the "cell is locked", how does the contents get deleted ?


You need a wend at the finish if the with block

Steve
The Active cell is not locked, so it can be deleted...

Thanks for the wend my typo

Martin

 

[MartinL]

Im still stuck on this, If anyone can see where Im going wrong as the colours do not change:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ActiveRange   As Range
Dim MyCell As String
Dim Workrange As String
Dim Flag As Long
Dim ActCellAddress As String

ActCellAddress = ActiveCell.Address

With ActiveCell
    MyCell = "V" & ActiveCell.Row
    Workrange = "B" & ActiveCell.Row & ":U" & ActiveCell.Row
    
    If Range(Workrange).Locked = True And ActiveCell = "" Then _
    Flag = 1
    If Flag = 1 Then _
    GoTo BAILOUT

    ActiveSheet.Unprotect

    If Range(MyCell).Value >= 1 Then _
        Range(Workrange).Interior.ColorIndex = 150 And _
        Range(ActCellAddress).Interior.ColorIndex = 120 And _
        Range(Workrange).Locked = True And _
        Range(ActCellAddress).Locked = False
        Flag = 2
    
    If Range(MyCell).Value = "" _
        Then Range(Workrange).Interior.ColorIndex = 7 And _
        Flag = 2
        
End With

BAILOUT:
    If Flag = 1 Then _
        MsgBox ("Only 1 Person Per shift can take holiday")
        
    If Flag = 2 Then _
        ActiveSheet.Protect


End Sub