Block certain cells in condition met in cell A

Clairexcel

New Member
Joined
Mar 23, 2021
Messages
31
Office Version
  1. 2016
  2. 2010
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Clairexcel

New Member
Joined
Mar 23, 2021
Messages
31
Office Version
  1. 2016
  2. 2010
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 .
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Mar 23, 2021
Messages
31
Office Version
  1. 2016
  2. 2010
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Clairexcel

New Member
Joined
Mar 23, 2021
Messages
31
Office Version
  1. 2016
  2. 2010
pratichegabriel1deskbak.xlsm
ABCDEFGHIJKLMNOPQRST
1New Project Yes/noMonthCustomerPartnerType of projectAgents involvedProject numberType of paymetStageDate of inputHours Exenses Expenses by agentDate of expenses Costo fase Total quoted/ to be cashed Already cashed inInvoice Date of invoiceTo be cashed untill balance
2   00€ -€ -€ -
3      00€ -€ -€ -
4      00€ -€ -€ -
5      00€ -€ -€ -
6     00€ -€ -€ -
7      00€ -€ -€ -
8      00€ -€ -€ -
9      00€ -€ -€ -
10      00€ -€ -€ -
11      00€ -€ -€ -
12      00€ -€ -€ -
13      00€ -€ -€ -
Pratiche
Cell Formulas
RangeFormula
C7:H13,C6:G6,C3:H5,H2,C2:D2C2=IF($A2="NO",C1,"")
K2:K13K2=SUMIFS($K$2:K$5000,$A$2:$A$5000,"<>si",$G$2:$G$5000,AI3)
L2:L13L2=SUMIFS($L$2:$L$5000,$A$2:$A$5000,"<>si",G$2:G$5000,AI2)
P2:P13P2=L2+O2
Q2:Q13Q2=SUMIFS($Q$2:$Q$4993,$A$2:$A$4993,"<>si",$G$2:$G$4993,AI2)
T2:T13T2=$P2-$Q2
 

Clairexcel

New Member
Joined
Mar 23, 2021
Messages
31
Office Version
  1. 2016
  2. 2010
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,029
Messages
5,639,624
Members
417,101
Latest member
amoverton2

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
Top