Repeat function on multiple columns

[kyhii]

Hi,

I have this macro that will unlock cell A2 when a correct value is entered in cell A1. Then, when a correct value is enter in cell A2, A3 will be unlocked. This will be repeated for few more rows. However, I would like to have the same function for column B to Z. How do i do this? I tried by changing the A2 to B2 and so on but the code is too long and also not a smart way of doing this. Please help. Thanks.

Please Login or Register  to view this content.

[rylo]

Hi

You will have to unlock the range B1:Z1 before you start this, but try the following. I'm not sure that I have it exactly as you want the formatting to go, but it should give you some idea how to move forward.

Please Login or Register  to view this content.

rylo

[kyhii]

Hi Rylo,

Thanks. That's what i'm looking for. However, i like to do some changes. What will the code be if i have the following conditions. I'm tried playing around using teh combination of offset and resize but to no success.

Please Login or Register  to view this content.

[rylo]

Hi

Assuming you want to go down 4 rows, then grab 4 rows from the current cell it would be

Please Login or Register  to view this content.

to get the range to action.

rylo

[kyhii]

Hi Rylo,

Thanks. It work well when i type YES in cell A2. However, it doesn't seem to work when there's a formula in A2. For example, if A1 bigger than 100, A2 will return a "YES", otherwise "NO". Any idea how to solve this? Thanks.

Please Login or Register  to view this content.

[rylo]

Hi

Can't see a mention of a formula in earlier posts.

How about you attach an example workbook that reflects your situation, and give some detailed steps on what is to be input, what is to happen, and what is to be the output.

rylo

[kyhii]

Hi Rylon,

The orginal workbook is very complicated. I created a more simplified version of what i'm what to do with the steps noted in sheet 1.

Thanks.

[rylo]

Hi

Would you want to put in another check so that if all 8 rows are filled for a column, then all those cells are locked? Or do you just want to have cells progressively unlocked in a column and then stay open?

rylo

[kyhii]

Hi Rylo,

Thanks a good question. The original workbook will be used as an drawing register which will be access by multiple people. Basically, everyone will choose his own layout and start filling the details in that layout column. Ideally, that person will have the opportunity to lock his own layout column once he's done. Not sure if that's possible or not. Otherwise, i'm happy just to keep the column unlock.

Thanks.

[rylo]

Hi

Here 'goes

Please Login or Register  to view this content.

rylo

[kyhii]

Hi Rylo,

Thanks for the quick reply. It's my fault not to specified earlier that the entire worksheet will be locked except for row 3 & 4. I copied and pasted the above code in and it doesn't seem to work. It only worked when the entire worksheet is unlocked at the start.

Thanks.

[rylo]

Hi

Change your protection options to allow users to select locked cells.

What is happening is that the moment you select an item from row 4, it immediately moves to the next column, so the test on the active cell column doesn't work.

rylo

[kyhii]

Hi Rylo,

Thanks for all your helps.