VBA lock and colour range except active cell

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
If the "cell is locked", how does the contents get deleted ?


You need a wend at the finish if the with block
 
Upvote 0
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
 
Upvote 0
Im still stuck on this, If anyone can see where Im going wrong as the colours do not change:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top