Marco that will run if the condition met

H

Harn88

Hello

I need some help with this please. I want to create a Marco that will run if
the condition met.


A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656

For example
If A1 equal to “Yes†then Cell B1 will be unprotected and if it’s equal to
“No†then the cell will remain protected. So after the Marco, the Cell
B1,B2,B3,B4,B6,B9 will be unprotected.

Do I need to create a Marco button for this or would it be possible just for
the Marco to run when Cell A1 equal to Yes.

Thank you very much!!!

Harn
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Unprotect myPassword
With Target

.Offset(0, 1).Locked = LCase(.Value) = "yes"
End With
Me.Protect myPassword
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
 
H

Harn88

Hi Bob

Thank you very much for your reply. Your Code work great but it is nearly
what I wanted. From my understanding, your code in the last e mail is when
input “yesâ€, the whole sheet will protect.

A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656


I would like to apology for not making the email clearer last time, below is
what I am trying to said in my last e mail.

My situation is the worksheet is already protected by my password (for
example my password is “Luckyâ€) however what I want is that if A1 equal to
“Yes†then Cell B1 will be unprotected only. The other Cell in the worksheet
will remain protected and if it is equal to “No†then the cell will remain
protected. So after the Marco, the Cell B1,B2,B3,B4,B6,B9 will be unprotected
only. i hope I make more sense this time.

Thank you very much for your help!!!

Best regards,

Harn
 
H

Harn88

Hi Bob

The example did not come out too great, number 1 to 9 is row number and A&B
is column. Sorry for the Confusion. So "yes" will be in Row 1 Column A and
the amount will bein Row 1 column B

Row/Column A B
1 yes 100
2 yes 656
3 yes 556
4 yes 964
5 no 632
6 yes 496
7 no 656464
8 no 656
9 yes 656

Thank you

best regards,

Harn
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top