If cells contain certain text, how to lock adjacent cells with Format cells -> Protection -> Locked

[K0st4din]

Hello, everyone,
I searched the internet for a solution to my case, but I can't get it to work.
I am looking for an option with which I suppose with a macro (maybe without a macro) a check is made to see if the numbers 1, 2 or 3 are contained in cells A36, 37, 38. If there are, then rows B36, 37, 38 should be locked.
The worksheet is locked, I am looking if it is possible to use Format cells -> Protection -> Locked .
Thank you in advance for your assistance!

АВАНСОВ ОТЧЕТ ИМЕ ФАМИЛИЯ МЕСЕЦ ГОДИНА(DOBAVQNE)-edited 01112023.xls
	A	B
4	2023	11
5
6	day	form
7	mounths	legend
8	1
9	2
10	3
11	4
12	5
13	6
14	7
15	8
16	9
17	10
18	11
19	12
20	13
21	14
22	15
23	16
24	17
25	18
26	19
27	20
28	21
29	22
30	23
31	24
32	25
33	26
34	27
35	28
36	29
37	30
38	1
ИМЕ СЛУЖИТЕЛ

Cell Formulas
Range	Formula
A8	A8	=DATE(godina,$B$4,1)
A9	A9	=DATE(godina,$B$4,2)
A10	A10	=DATE(godina,$B$4,3)
A11	A11	=DATE(godina,$B$4,4)
A12	A12	=DATE(godina,$B$4,5)
A13	A13	=DATE(godina,$B$4,6)
A14	A14	=DATE(godina,$B$4,7)
A15	A15	=DATE(godina,$B$4,8)
A16	A16	=DATE(godina,$B$4,9)
A17	A17	=DATE(godina,$B$4,10)
A18	A18	=DATE(godina,$B$4,11)
A19	A19	=DATE(godina,$B$4,12)
A20	A20	=DATE(godina,$B$4,13)
A21	A21	=DATE(godina,$B$4,14)
A22	A22	=DATE(godina,$B$4,15)
A23	A23	=DATE(godina,$B$4,16)
A24	A24	=DATE(godina,$B$4,17)
A25	A25	=DATE(godina,$B$4,18)
A26	A26	=DATE(godina,$B$4,19)
A27	A27	=DATE(godina,$B$4,20)
A28	A28	=DATE(godina,$B$4,21)
A29	A29	=DATE(godina,$B$4,22)
A30	A30	=DATE(godina,$B$4,23)
A31	A31	=DATE(godina,$B$4,24)
A32	A32	=DATE(godina,$B$4,25)
A33	A33	=DATE(godina,$B$4,26)
A34	A34	=DATE(godina,$B$4,27)
A35	A35	=DATE(godina,$B$4,28)
A36	A36	=DATE(godina,$B$4,29)
A37	A37	=DATE(godina,$B$4,30)
A38	A38	=DATE(godina,$B$4,31)

Named Ranges
Name	Refers To	Cells
godina	='ИМЕ СЛУЖИТЕЛ'!$A$4	A8:A38

Cells with Data Validation
Cell	Allow	Criteria
B8:B38	List	=$B$40:$B$46
A4	List	=$T$2:$T$32
B4	List	=$U$2:$U$13

 

[K0st4din]

Hi, I keep trying and putting things together somehow, but apparently I'm not doing it right, but the idea is something like this macro I'm trying to put together.

Private Sub Worksheet_Change(ByVal Target As Range)
    
     Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="123" '->>> How to say for Sheet name i.e "Banana"

    If Range("A36:A38") = ">=3" Then '->>> How to say for Sheet name i.e "Banana"
        Range("B36:B38").Locked = False
    ElseIf Range("A36:A38") = "<3" Then
        Range("B36:B38").Locked = True
    End If
    ActiveSheet.Protect Password:="123"
     Application.ScreenUpdating = True
End Sub

I'm hoping someone can help me because I'm messing up somewhere or not doing things right.
Thank you again in advance for your help!
P.S -
And it may not be true, my idea.
That is, after experimenting, it turns out that I need to check each of the displayed cells in the A column, and if there is a 1, 2 or 3 somewhere, then lock exactly the adjacent cell.
That is, if A38 has 1, 2 or 3 to lock B38, the same for A37, A36.
This is the correct one, because with the macro I'm trying to do, it's like saying wherever you find 1,2,3 lock, but maybe for example in A36=29, and in A37=30, then I only have to lock A38

 

[K0st4din]

In reality, the following is obtained:
In a worksheet named Bananas (which is locked), I need to check in cells A36, A37, A38 whether the numbers 1 to 3 are contained, if any of the cells contain the numbers, the adjacent cell respectively B36, B37, B38, to be locked and cannot be used by the user.

 

[Joe4]

In order for a "Worksheet_Change" event procedure to fire automatically, you always put it in the Sheet module of the Sheet that you want to run it against.
You typically do NOT need a Sheet reference in your VBA code (if you leave it off, it assumes whatever sheet it is running against, "Bananas" in this case).
So you would need need a Sheet reference in this code unless multiple sheets are involved in your code.

You have an issue with how you are checking the values in your range.
You cannot check a multi-cell range with a single inequality, and you inequality check cannot be written as text.

When do you want this code to fire?
A Worksheet_Change event procedure fires when a cell on your sheet is MANUALLY updated.
So, are you trying to run this as someone MANUALLY updates a cell in the range A36:A38?
If not, you need to give us more details of when this automated code should fire.

 

[Joe4]

If you wanted your VBA code to run when a cell in range A36:A38 is manually updated, this is what your VBA code would look like:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
    Dim cell As Range
    
'   Check to see if update made to range A36:A38
    Set rng = Intersect(Target, Range("A36:A38"))
    
'   Exit code if no changes made to watched range
    If rng Is Nothing Then Exit Sub
    
'   Unprotext sheet
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="123" '->>> How to say for Sheet name i.e "Banana"

'   Loop through watched cells that were updated
    For Each cell In rng
'       See if value of cell is greater than or equal to 3
        If cell.Value >= 3 Then
            Range("B36:B38").Locked = False
'       See if value of cell is less than 3
        ElseIf cell.Value < 3 Then
            Range("B36:B38").Locked = True
        End If
    Next cell
    
'   Reprotect sheet
    ActiveSheet.Protect Password:="123"
    Application.ScreenUpdating = True
    
End Sub

 

[K0st4din]

Hello,
already in the first post, I uploaded a part of the table, in which it can be seen that exactly in this column and the range I have made when I choose from B4, the month, the days are arranged downwards and the Saturdays and Sundays are colored. However, in many cases, after the 30th, 1 appears again, and in February it stops either on the 28th or the 29th, and down at the 28th, 1,2,3 are displayed.
People start writing in these 1,2,3 as well, but in column B. And I want exactly that, to stop it.
That is, when they choose their month, they can write until the 30th or 31st, and then the last cells are locked.
That is, in this case we are not talking about manually writing 1,2,3, but it is done through the formulas that change the numbers (dates).

 

[Joe4]

It sounds like maybe we should make this run when cell B4 is manually updated. Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
  
    Dim rng As Range
    Dim cell As Range
  
'   Check to see if update made to cell B4
    Set rng = Intersect(Target, Range("B4"))
  
'   Exit code if no changes made to watched range
    If rng Is Nothing Then Exit Sub
  
'   Unprotext sheet
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="123"

'   Loop through range A36:A38
    For Each cell In Range("A36:A38")
'       See if value of cell is greater than or equal to 3
        If cell.Value >= 3 Then
            cell.Offset(0, 1).Locked = False
'       See if value of cell is less than 3
        ElseIf cell.Value < 3 Then
            cell.Offset(0, 1).Locked = True
        End If
    Next cell
  
'   Reprotect sheet
    ActiveSheet.Protect Password:="123"
    Application.ScreenUpdating = True
  
End Sub

 

[K0st4din]

Hello again,
I will try to explain it like this:
I open the workbook
I'm in the Bananas example worksheet (mine has a different name)
In cell B4, I have made a drop-down menu in which the 1 to 12 months of the year can be selected (like here, it can also be typed from the keyboard). That is, in both options I choose a given month.
Then in column A, the days are arranged - 1st, 2nd, 3rd until the end or until the 30th, or until the 31st or if it is February until the 28th, 29th.
I don't know how to say it, but I want, whether the month is selected from the dropdown menu or the month is typed in B4 manually, to count and lock the specific cells that are 10000% below the 30th, the number 1, or if it's February from 1 to 3.


The other thing that puzzles me is that this password is 123, I try to change it to another and it shows me this error (and I am sure that the password is not written incorrectly)

 

[Joe4]

The code I provided will do all that you say. It does not matter if the value in cell B4 is typed manually or selected from a drop-down box, the code will run.

However, for this code to run automatically, it MUST be placed in the VBA module of sheet you want it to run against. The easiest way to ensure that it is doing that is to do the following:
1. Go to your "Bananas" sheet
2. Right-click on the sheet tab name at the bottom of the screen
3. Select "View Code"
4. Paste the VBA to the VB Editor window that pops up

However, if you get the error message above, then that means that the code is already trying to run. So I suspect you may already have the code in the correct place, and you just have some password issue. If you change the password on the sheet, you will need to change it in the VBA code too.

 

[K0st4din]

Yes, I have put it in the correct worksheet.
And when I select, for example, month 2 from B4, it should A36, A37 and A38 with numbers less than 3 (ie 3,2,1) be locked B36, 37, 38. But it doesn't.
I remain for comment.
Thanks in advance.