Lock/unlock/color a Range based on the Value in another Range

[Closed]
Report
Posts
1
Registration date
Thursday September 19, 2013
Status
Member
Last seen
September 19, 2013
-
Posts
2782
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 2, 2021
-
Hello,
My questions relate to Excel for MAC 2011 (not PC), I am very new at VBA. I found the below code and need help to make it work.

My Excel VBE wants to name the Routine in the format: SUB Name(). But I see many examples with "Private Sub Worksheet_Change(ByVal Target As Range)" which my Excel will not accept as a name.

But it is seemingly used as a name in all the examples I find. What is the issue here?

I have an old version of the book Excel VBA for Dummies and I can get one of his tutorial routines to work - it uses the SUB Name () format.

Any recommendation for a current book that addresses Excel for MAC 2011?

Once I get this working, I also want to change the "B1" Range cell colors based on the Value in "A1": It must run whenever the Workbook is open.

If Range("A1").Value = 1 Then
.Range("B1").Locked = False
ElseIf
.Range("B1").Locked = True
End If

End Sub

Help would be very much appreciated!

1 reply

Posts
2782
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 2, 2021
468
Hi Phstol,

There are different ways to execute/run a code.

One way is to create a module. Start of the code could be "Sub Name". Now you can manually run the code/macro called Name.

Another way is to put code in worksheet. Start of code could then be "Private Sub Worksheet_Change(ByVal Target As Range)". The code will now run whenever a change is made to the sheet.

Change your posted part of code into:
If Range("A1").Value = 1 Then
Range("B1").Locked = False
Else
Range("B1").Locked = True
End If

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!