Block certain cells in condition met in cell A

[Clairexcel]

Hi there,
trying to figure out step by step how to build macros and learn more.
Now given that my other code was a mess, I figured I would simplify things. So let the formulas in the cells of my worksheet, but I would like a vba to perfrom the following:
1. My worksheet goes from A to T5000
2. If a user enters YES in cell A of a row, then I would like to block cells K, L, P, Q and T on that row, so that users can not enter anything in those cells.
3. Otherwise, if they they enter "no" in cell A, then the K, L, , Q should be unblocked in that row and they should be able to enter numbers. Cells P and T should always remain blocked though.
4. Also if cell A contains "no", I would like vba to automatically copy C to H range from the row above.
Is there any way I can record macros and then create a event macro with conditions if so then perform macro1, else perform marcro2"" ? How can I create such macros?
Do I have to create event? If so, can you please help me as in how to do it?
Thank you so very much.

 

[Clairexcel]

I found a code from Joe4 in a post from ferbuary This code seems to be built on to do what I need, but it does not work. Because if I protect the sheet, it will block all columns starting from the offset .

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not (Intersect(Target, Range("K2:K22")) Is Nothing) And Target.Cells.Count = 1 Then
        ActiveSheet.Unprotect
        If Target.Value = "Disagree" Then
            Target.Offset(0, 1).Locked = False
        Else
            Target.Offset(0, 1).Locked = True
        End If
        ActiveSheet.Protect
    End If

End Sub

Anyone any idea please? Thank you

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      If LCase(Target.Value) = "yes" Then
         Intersect(Target.EntireRow, Range("K:L,P:Q,T:T")).Locked = True
      ElseIf LCase(Target.Value) = "no" Then
         Intersect(Target.EntireRow, Range("K:L,Q:Q")).Locked = False
         Target.Offset(, 7).Value = Target.Offset(, 2).Value
      End If
   End If
End Sub

 

[Clairexcel]

Hi there Fluff thank you so much for answering! I have been trying all day to get a code for this problem and I am still stuck! So thanks
Unfortunately your code does not seem to do anything. It doesn't give me errors though, just does not block cells...what am I doing wrong?
Again, thank you

 

[Fluff]

Did you put the code into the sheet module for the sheet it should work on?

 

[Clairexcel]

Did you put the code into the sheet module for the sheet it should work on?

Yes yes, I did, it is a vba event ( I learnt this from this forum

 

[Fluff]

And you are manually changing single cells in col A to either yes or no?

 

[Clairexcel]

Did you put the code into the sheet module for the sheet it should work on?

Here is my minisheet, if it can help you..

 

[Clairexcel]

pratichegabriel1deskbak.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	New Project Yes/no	Month	Customer	Partner	Type of project	Agents involved	Project number	Type of paymet	Stage	Date of input	Hours	Exenses	Expenses by agent	Date of expenses	Costo fase	Total quoted/ to be cashed	Already cashed in	Invoice	Date of invoice	To be cashed untill balance
2											0	0				€ -	€ -			€ -
3											0	0				€ -	€ -			€ -
4											0	0				€ -	€ -			€ -
5											0	0				€ -	€ -			€ -
6											0	0				€ -	€ -			€ -
7											0	0				€ -	€ -			€ -
8											0	0				€ -	€ -			€ -
9											0	0				€ -	€ -			€ -
10											0	0				€ -	€ -			€ -
11											0	0				€ -	€ -			€ -
12											0	0				€ -	€ -			€ -
13											0	0				€ -	€ -			€ -
Pratiche

Cell Formulas
Range	Formula
C7:H13,C6:G6,C3:H5,H2,C2:D2	C2	=IF($A2="NO",C1,"")
K2:K13	K2	=SUMIFS($K$2:K$5000,$A$2:$A$5000,"<>si",$G$2:$G$5000,AI3)
L2:L13	L2	=SUMIFS($L$2:$L$5000,$A$2:$A$5000,"<>si",G$2:G$5000,AI2)
P2:P13	P2	=L2+O2
Q2:Q13	Q2	=SUMIFS($Q$2:$Q$4993,$A$2:$A$4993,"<>si",$G$2:$G$4993,AI2)
T2:T13	T2	=$P2-$Q2

 

[Clairexcel]

And you are manually changing single cells in col A to either yes or no?

yes, I manually change A cells, typing yes or no. I tried to lock the cells first (the range K to T) but it still doesn't work