Using VBA to change cell properties into lock

[TPortsmouth]

Hello,

I've an issue on how to change other cells into "Lock" based on a specific value.

By default, cells within columns B to F was locked, if relevant row's Control value = "Yes", the same row's Column C, D and E will be unlock.
For example, if Cell A2 = "Yes", then Cell C2:E2 will be unlock.

I've been thinking whether this can be done by a series of Macro below:
1. If the cell value of A2 change, then after update, if A2 is not equal to "Yes", nothing happen.
2. If A2 = "Yes", unlock worksheet with password.
3. Cell properties of C2:E2 change into "Unlock".
4. Lock worksheet with password.

One of the difficulties is: How do I define the range? As above range A2:F6 is just an example, the whole data set can be much larger.
Let's say, it can contain more than a 1000 records.

Any suggestion on this? Thanks.

 

[MrMarho]

Hello TPortsmouth,

I would say use a table to ensure your data stays in a dynamic range but. The issue I have is adopting a static lock code with a dynamic one. Follow the link below to check out this thread. It will help immensely

Locking and unlocking cells based on value in another cell

 

[TPortsmouth]

Hi MrMarho,

Thanks for your referral, that's a good reference, I am currently digesting it.

Cheers.