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

Closed
PHSTOL Posts 1 Registration date Thursday September 19, 2013 Status Member Last seen September 19, 2013 - Sep 19, 2013 at 12:03 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Sep 24, 2013 at 12:10 PM
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

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Sep 24, 2013 at 12:10 PM
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
0