Too many ELSEIF statements

[gord9b]

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?

 

[Jim North]

Range("c4:c" & range("b2").value + 4).Locked = False

 

[Damon Ostrander]

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

 

[gord9b]

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