Results 1 to 4 of 4

Thread: How to lock specific row if a certain value exists in one cell?

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14

    How to lock specific row if a certain value exists in one cell?

    Hi,

    How do I lock a row, if in column C a certain value exists?
    In the attached sheet I have a daily employee attendance sheet, if an employee left or resigned the same is shown in column C using a formula. Is there a way to lock the specific row if it has a value Left or Resigned?



    Thank
    Rajesh
    Attached Files Attached Files

  2. #2
    Hi Rajesh,

    I have put the following code together with should work but VBA isn't my best subject . The problem is going to be when you want the code to run and also in order for locked cells to take effect the workbook must be protected which yours is not. Left me know what you think and we can change the code to suit.

    Code:
    Sub LockNonactive()
    
    Dim nonAct As Range
    Dim CheckValue As String
    
    Set nonAct = ActiveSheet.Range("C3:C10")
    CheckValue = "Left"
    CheckValue = "Resigned"
    
    ActiveSheet.Unprotect
    
    For Each Cell In nonAct
    If Cell.Value = CheckValue Then Cell.EntireRow.Locked = True
    Next
    
    ActiveSheet.Protect
    
    
    End Sub

  3. #3
    Member rollis13's Avatar
    Join Date
    Nov 2012
    Posts
    36
    Rep Power
    0
    Just a little fix to xander1981's code:
    Code:
    Sub LockNonactive()
        Dim nonAct As Range
        Dim CheckValue1 As String
        Dim CheckValue2 As String
        Dim cell As Range
        Set nonAct = ActiveSheet.Range("C3:C10")
        CheckValue1 = "Left"
        CheckValue2 = "Resigned"
        ActiveSheet.Unprotect
        For Each cell In nonAct
            cell.EntireRow.Locked = False
            If cell.Value = CheckValue1 Or cell.Value = CheckValue2 Then cell.EntireRow.Locked = True
        Next
        ActiveSheet.Protect
    End Sub

  4. #4
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Hi Xander and Rollis,

    Thanks for your help. It worked

    Thanks
    Rajesh

Similar Threads

  1. Delte a specific column and does not delete the top row
    By jffryjsphbyn in forum Excel Help
    Replies: 1
    Last Post: 06-13-2013, 02:00 PM
  2. How To Lock Or Protect Cell Using Data Validation
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-06-2013, 01:57 AM
  3. Highlight Active Cell’s Row and Column
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  4. Lookup From Cell Range By Matching Row and Column
    By paul_pearson in forum Excel Help
    Replies: 2
    Last Post: 03-07-2013, 02:02 PM
  5. How to Lock or Unlock row basis previous cell input?
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 07-25-2012, 02:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •