# Too many ELSEIF statements

#### gord9b

##### Board Regular
I currently have a macro that unlocks a range of cells depending on the numeric value of an input cell. It looks like this:

[c4:c19].Locked = True
If [b2] > 15 Then
[c4:c19].Locked = False
ElseIf [b2] > 14 Then
[c4:c18].Locked = False
ElseIf [b2] > 13 Then
[c4:c17].Locked = False
ElseIf [b2] > 12 Then
[c4:c16].Locked = False
ElseIf [b2] > 11 Then

As you can see, this is unwieldy. If I want to expand the range of cells, then the number of ElseIf statements becomes unwieldy.
Is there a way to insert the cell value in the logic, such as:
[c4:"value in cell b2")].Locked = False
where the second parameter would be the numeric value of cell b2?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Range("c4:c" & range("b2").value + 4).Locked = False

Hi Gordon,

You can generalize

[c4:"value in cell b2")].Locked = False

as follows:

Range([C4],Cells([B2].Value,3)).Locked = False

where B2 contains the row number and the 3 refers to column C. And of course you can offset the value in B2 to replace your set of If...ElseIf tests:

Range([C4],Cells([B2].Value+4,3)).Locked = False

Thanks to both of you for your prompt replies. Both responses are great.

Replies
1
Views
309
Replies
6
Views
433
L
Replies
8
Views
705
Legacy 352679
L
Replies
1
Views
422
Replies
1
Views
242

1,219,770
Messages
6,150,167
Members
450,937
Latest member
kattyg261

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back