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

 

[Joe4]

It works perfectly fine for me.
Have you accidentally disabled events from running in your code?
Try MANUALLY running the following code to re-enable them:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

And add the lines in red to your current code for debugging:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim rng As Range
    Dim cell As Range
   
    MsgBox "VBA code is running!"
   
'   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
   
    MsgBox "Cell B4 was manually updated!"
   
'   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

Now try updating cell B4. You should get the two Message Boxes. If you do not, then you have done something wrong, most likely:
1. Have placed the VBA code in the wrong module
2. Have not enabled VBA code to run
3. Are not updating cell B4 on your sheet manually
4. Are working across multiple sheets instead of just within one sheet

 

[K0st4din]

Yes, with what you added, everything says that the macro is working. That manually changed B4, etc., but when I unlock the worksheet, the picture shows that there is no beak, the cells are locked, and all three cells meet the condition.

PS - I just didn't understand, the last sentence, that I work in several books, and I am only in this workbook and in this worksheet. Except I have 5 more but I'm not interested in them right now. This Bananas worksheet of mine is called "EMPLOYEE NAME" but I'm changing it.


If I may say again, if there are 1, 2 or 3 in A, cells B - they cannot write. This is the shortest explanation

 

[Joe4]

In going back over the sheet, I think I see your issue - you do NOT have numbers like 1,2,3 in cells A36:A38.
Rather, you have date formulas that are formatted just to show the day portion. So you cannot use a simple check of the displayed value.
Formulas run off the actual underlying values, not the displayed values.

So we need to pull the "Day" value off to do our check, i.e.

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 Day(cell.Value) >= 3 Then
            cell.Offset(0, 1).Locked = False
'       See if value of cell is less than 3
        ElseIf Day(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]

Yes, yes, yes, now it works perfectly. This is exactly what should have happened. At the very beginning, when I uploaded the table, I thought that it would be seen that I had formulas there and that I was taking a day. Apparently that's where the confusion about these numbers came from. It's perfect now. If I could, I would give countless likes and thanks for this help! I wish you good health and many happy moments. Thank you from the bottom of my heart!

 

[Joe4]

You are welcome! I am glad I could help!