If a column and cell contain a word, lock the row

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
488
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello, everyone,
I searched the internet and found something similar to what I am looking for, but it is not accurate.
I'm looking for a way if in column B8:B38 contains the letter O in a cell, to lock the whole row in the range(row) D:G.
Example: If B12 is O, then Lock rows D12:G12 and ect....
What I found is the following, but not exactly
I ask for your cooperation
Thanks in advance
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1") = "Accepting" Then
        Range("B1:B4").Locked = False
    ElseIf Range("A1") = "Refusing" Then
        Range("B1:B4").Locked = True
    End If
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Are cells B8:B38 compiled by manual entry or by a formula?

Bye
 
Upvote 0
On top of what @Anthony47 has asked how exactly do you want you want the macro to be triggered? by a change in the cell , by a button, by the worksheet opening or something else?

and are there any cells in the range that contain "O" anywhere in the cell?
 
Upvote 0
Hello,
there are no letters in the cells. Each month on specific days will be recorded manually.
In this case we are talking about a cell change.
in column B8:B38 I have a drop-down menu and several options, but when option "O" is selected from the drop-down menu, to disable in the line to write in the mentioned range D:G
I remain available if you have more questions
thank you very much
2020-09-05_180326.jpg
 
Upvote 0
If it is from a dropdown then try one of the codes (comment out the 2 protect/unprotect lines if you don't want the sheet protected now)

Code goes in the worksheet module (right click the sheet tab and click view code). Please note that you can have only one Worksheet_Change event in each sheet.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B8:B38")) Is Nothing And Target.CountLarge = 1 Then

        ActiveSheet.Unprotect 'add password if required
        Application.EnableEvents = False

        If UCase(Target.Value) = "O" Then
            Intersect(Target.EntireRow, Columns("D:G")).Locked = True
        Else
            Intersect(Target.EntireRow, Columns("D:G")).Locked = False
        End If

        Application.EnableEvents = True
        ActiveSheet.Protect 'add password if required

    End If

End Sub

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B8:B38")) Is Nothing And Target.CountLarge = 1 Then
    
        ActiveSheet.Unprotect 'add password if required
        Application.EnableEvents = False
        
        If UCase(Target.Value) = "O" Then
            Cells(Target.Row, "D").Resize(, 4).Locked = True
        Else
            Cells(Target.Row, "D").Resize(, 4).Locked = False
        End If
        
        Application.EnableEvents = True
        ActiveSheet.Protect 'add password if required
    
    End If
    
End Sub
 
Upvote 0
@MARK858 will forgive me if I propose this variation of his code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range
Application.EnableEvents = False
For Each myC In Target
    If Not Intersect(myC, Range("B8:B38")) Is Nothing Then
        ActiveSheet.Unprotect 'add password if required
        If UCase(myC.Value) = "O" Then
            Cells(myC.Row, "D").Resize(, 4).Locked = True
''            Cells(myC.Row, "D").Resize(, 4).Interior.Color = RGB(255, 255, 150)    'color protected cells
        Else
            Cells(myC.Row, "D").Resize(, 4).Locked = False
''            Cells(myC.Row, "D").Resize(, 4).Interior.Color = xlNone
        End If
    End If
Next myC
Application.EnableEvents = True
ActiveSheet.Protect 'add password if required
End Sub
The only difference is that it manages changes in multiple cells; for example if you clear two or more cells in the Range("B8:B38") the original code will not remove the lock on columns D:G, this variant will remove it. The same if you copy something in two or more cells
The commented lines could be used to visually highlight the protected areas

A final remark: the worksheet has to be manually formatted for non protection on the whole cells.

Bye
 
Upvote 0
@Anthony47, personally I would prefer it if there was an easy way of preventing clearing/pasting in Data Validated cells, kinda defeats the purpose of validating them in the first place ;) and no I never object to people giving suggestions on codes.
 
Upvote 0
Hello guys,
all the codes are wonderful.
I am very grateful to you, and I decided to try all three varieties, for the end I chose the Anthony47 code, because it changes color if it does not contain the letter O (because sometimes mistakes happen and if the letter is changed, it will be without color) :)
As for the password - Yes, I have and have added it. :)
I always said you were amazing.
Be alive and healthy and thank you once again.
P.S - One last question that comes to my mind right now - Can I add a second letter - for example O and B?
 
Upvote 0
You're welcome.
Btw you are right to go with the @Anthony47 code but mainly for the multiple cells part he stated rather than the colouring.

As for the 2nd letter just change
VBA Code:
If UCase(myC.Value) = "O" Then
to
VBA Code:
If UCase(myC.Value) = "O" Or UCase(myC.Value) = "B" Then
 
Upvote 0
Yes, yes, precisely because if a person makes a mistake in a cell, it will be possible to correct the letter.
Thanks for the addition for both letters and thank you both for the macros.
Once again, be alive and healthy.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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