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.

 

[Fluff]

Ok try this

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
         MsgBox "Yes"
         Intersect(Target.EntireRow, Range("K:L,P:Q,T:T")).Locked = True
      ElseIf LCase(Target.Value) = "no" Then
         MsgBox "No"
         Intersect(Target.EntireRow, Range("K:L,Q:Q")).Locked = False
         Target.Offset(, 7).Value = Target.Offset(, 2).Value
      Else
         MsgBox Target.Value
      End If
   End If
End Sub

and change a value in col A, do you get a mesage box? If so what does it say?

 

[Clairexcel]

Ok try this

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
         MsgBox "Yes"
         Intersect(Target.EntireRow, Range("K:L,P:Q,T:T")).Locked = True
      ElseIf LCase(Target.Value) = "no" Then
         MsgBox "No"
         Intersect(Target.EntireRow, Range("K:L,Q:Q")).Locked = False
         Target.Offset(, 7).Value = Target.Offset(, 2).Value
      Else
         MsgBox Target.Value
      End If
   End If
End Sub

and change a value in col A, do you get a mesage box? If so what does it say?

Ok, I tried it. Everytime I type a values in column A (i type yes or no) a msg box appears and shows what I typed, yes or no. I want to specify that my sheet is unlocked and also I have unchecked the Locked option in Cell Format.
Am I supposed to lock them? Because when I did lock the wksh the code gave me an error on the following line;
Intersect(Target.EntireRow, Range("K:L,Q:Q")).Locked = False

 

[Fluff]

Yes the sheet does need to be protected & I forgot that part of the code.
Try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Me.Unprotect "password"
      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
      Me.Protect "password"
   End If
End Sub

Change password to whatever your pass word is

 

[Clairexcel]

Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Column = 1 Then Me.Unprotect "password" 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 Me.Protect "password" End If End Sub

Ok, I protected the sheet with a password. Please note that I first made sure to uncheck the Locked option from on all the cells on the sheet.
I added your code, but unfortunately it does nothing, it lets me type in A column whateve I want but it does not lock the corresponding cells in the row (the ones in the column K, L etc)...
EDIT: It works @Fluff you are a wizzard. It was my fault because after protecting the sheet I had to close the file and reopen it. You saved my day, thank you so so much!!!

 

[Fluff]

Lock the columns that users should not have access to & then try the code.

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[Clairexcel]

@Fluff can I bother you with one more question?
The line that is supposed to copy the values of the cells C:H from the row above, if value in A is no, is working only for the first row (related to A2, then it does not do anything...

 

[Fluff]

Oops misread your op, thought you wanted to copy C into H for the target row.
Try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Me.Unprotect "password"
      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(-1, 2).Resize(2, 6).FillDown
      End If
      Me.Protect "password"
   End If
End Sub

 

[Clairexcel]

Oops misread your op, thought you wanted to copy C into H for the target row.
Try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Me.Unprotect "password"
      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(-1, 2).Resize(2, 6).FillDown
      End If
      Me.Protect "password"
   End If
End Sub

No problem, you have given me great stuff. the last code does copy row above if value in a is no, but only C and D..
I want that if I put no in cell A 4 (for example), then the code shoud copy in cells C4:H4 the values from the cells C3:H3. And if I input no in A5 the cells from C5: h5 to copy the values/texts from the C4:H4.
It is to avoid users's error possibility..
so I suppose I must modify this line and change the offset...but I do not know which ones do what..
Target.Offset(-1, 2).Resize(2, 6).FillDown

 

[Fluff]

That code should copy cells C:H
Do you get any error messages & are there values in E:H?