macro that locks down cells based on a what is shown in another cell

[caliskier]

Hi,

I have a workbook with several sheets, but just on the sheet called “sales” I need a macro to do the following:

If F4 = CST
Then Lock down H4-S4 and AM4-AZ4

But if F4 is changed to something else I need H4-S4 and AM4-AZ4 to be unlocked

Is a password necessary for this? It is ou

I just want it to lock and unlock, not protect and unprotect.

Thanks for any help.

[StephenR]

Right-click on the worksheet tab, View Code and paste this code:

Please Login or Register  to view this content.

[VBA Noob]

U can add the Ucase statement to allow for upper and lower case entry to StephenR code.

Please Login or Register  to view this content.

VBA Noob

[caliskier]

Humm, neither of these are working. I have this running in "this workbook" could that be messing it up for me?

Please Login or Register  to view this content.

Thanks again

[VBA Noob]

caliskier,

The sheet is protect. Try this. I've added a colour line so you can see if it works. Add the extra protection you may require below

Please Login or Register  to view this content.

VBA Noob

[caliskier]

caliskier,

The sheet is protect. Try this. I've added a colour line so you can see if it works. Add the extra protection you may require below

Please Login or Register  to view this content.

VBA Noob

Still not working, no color change or locking.

[caliskier]

By the way, CST is selected on another sheet, and linked to this sheet where it still says CST does that make a difference?

[caliskier]

Ok, I am seeing some results, got it to work when I type in CST on the sales sheet, need it to accept the link though. It is linked from Orders.

[caliskier]

I am killing this topic and abandoning it. I did get it to work but for only one line, we have several lines to do it for. We will just manually lock as it will not change onece it is set. Thanks Noob for all your help. I am learning more and more, soon I will be able to help others.

[jindon]

Basic mistake
The line

Please Login or Register  to view this content.

means if any cell changes to the value same as Range("F4"), it will execute.
e.g If Range("F4") = Empty and you delete the contents of any other cell, it will execute.

Please Login or Register  to view this content.

[VBA Noob]

Formulas won't work with change events (See link for more) then see attached. Change sheet2 cell A1 and then look at sheet 1

http://www.cpearson.com/excel/Events.aspx

jindon,

I normally use

Please Login or Register  to view this content.

VBA Noob