• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Locking/ Unlocking cell based on the cell with formula

memeash

New Member
Hihi

I have some columns (Q - U) that is running on formula linked to column P. Depending on the value generated in column P, the responses under columns Q - U differs. When Q - U yield "N.A.", i would like excel to lock the cells.

Eg. if P5 is showing "Sale", the the corresponding S5 and T5 should be unlocked, but Q5, R5, and U5 will be locked.

Any help is appreciated, thanks so much!
 

Attachments

  • lock cells.xlsx
    10.5 KB · Views: 8
Here a possibility
Take care that the sheet with data is protected (with no password)
In the sheet with data put next code

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rg As Range
    If (Target.Column <> 9) Then Exit Sub
    For Each Rg In Cells(Target.Row, "Q").Resize(1, 5)
        Application.EnableEvents = False
        ActiveSheet.Unprotect
        If (Rg = "N.A.") Then Rg.Locked = True
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Application.EnableEvents = True
    Next Rg
End Sub
Option Explicit
 

Attachments

  • lock cells2.xlsm
    18.1 KB · Views: 8
thanks so much for your help PCI! it works but i have 2 slight problems

1) I forget to mention earlier that Columns A to I are actually copied in from another spreadsheet and usually multiple rows are copied and pasted at 1 go. the codes work only for 1st row that but not for subsequent rows that were pasted in simultaneously. can you help to revise the codes to overcome that?

2) column P is running on formula reading to column I, but sometimes column P could be override manually by a user. currently once the codes runs and lock the respective cells, it does not unlock when column P changes.

eg if P5 is showing "Sale" via formula, the corresponding S5 and T5 would be unlocked, Q5, R5, and U5 would be locked. but if User changes P5 manually to "refinancing", i would like Q5 and R5 to unlock, and S5, T5 U5 to lock (the cells that generated "N.A." after the change). are we able to do that?
 
  • Like
Reactions: PCI
Here a remake of the macro to take care of your 2 comments
Test and comment
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rg As Range, TRg  As Range
    For Each TRg In Target
        If ((TRg.Column <> 9) And (TRg.Column <> 16)) Then Exit Sub      '  For  Column  "I"  and "P"
        For Each Rg In Cells(TRg.Row, "Q").Resize(1, 5)
            Application.EnableEvents = False
            ActiveSheet.Unprotect
            If (Rg = "N.A.") Then
                Rg.Locked = True
            Else
                Rg.Locked = False
            End If
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            Application.EnableEvents = True
        Next Rg
    Next TRg
End Sub
 
Back
Top