Lock entire row based on cell value which updates by a formula, not entry

[bgrier85]

I have seen many posts for locking a row based on the value in column A, for example. This works if I manually change the value in column A. However, I am trying to determine the coding so that if I have an If statement in column A, returning the values "x" or "", for true or false respectively, that would allow the macro to run and lock the entire row is an "x" is the value of the cell in column A of that row. To further clarify, I have a sheet which employees select vacation time. Column B lists the 365 days of the year. I have another cell which automatically populates today's date. Then, column A check if the corresponding date in column B is < today's date, and returns an "x". I would then like the sheet to lock all rows with a "x" in column A (which would mean the date is in the past, so employees can not go back and remove their entries). How can this be done? From what I've gathered, the Change Event does not work when the target is changed by a formula. Any ideas?

 

[VoG]

Perhaps this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Me.Unprotect
For i = 1 To LR
    With Range("A" & i)
        If .Value = "x" Then .EntireRow.Locked = True
    End With
Next i
Me.Protect
End Sub

 

[bgrier85]

Yes, that worked perfectly. Thank you very much. Just one follow up question, I was using activesheet.protect to protect the sheet. I have not seen the language me.protect before. What is the difference between the two. Thanks.

 

[VoG]

In this case they are the same. For me, Me is quicker to type

You can only use Me where the context is obvious (to VBA) - for example in event code or a userform initialize procedure.

 

[Tahas]

Unfortunately, this didn't work me. I followed your instruction.... as right click the sheet tab, view code, paste the code but still didn't work. Please help.

Thanks,
Taha

Perhaps this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Me.Unprotect
For i = 1 To LR
    With Range("A" & i)
        If .Value = "x" Then .EntireRow.Locked = True
    End With
Next i
Me.Protect
End Sub

 

[Tahas]

Sorry just an update:
1. Code only works upon entering a date formula ="NOW() in Column B whether there is an "x" in Column A or not.
2. Code locks the entire sheet. Is that possible to lock only those Rows in Column A which have "x"?

Much appreciate!
T