VBA lock and colour range except active cell

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
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
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
If the "cell is locked", how does the contents get deleted ?


You need a wend at the finish if the with block
 

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
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

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
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
 

Forum statistics

Threads
1,081,560
Messages
5,359,609
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top