Locking a Range of Cells Based on Other Cell Values

[_Ryan_]

I have a worksheet that I want to have different ranges of cells locked, based on a different cells value. What I have right now is something that just locks an individual cell. I can't seem to figure out how to adjust the code to make it work for a range. It is probably very easy, but I cant figure it out. I have a few other ranges that I would want to apply this to, instead of adding the current code I have to each individual cell. But if I could have someone adjust my code for this range, I could adjust it for the others.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.Address = "$B$24" Then
If [B6] < "2" Then
[B24]
MsgBox "Invalid amount of" & vbCrLf & _
"outpatient visits", 64, "Access into cell not allowed."
End If
End If
End Sub

The cell that it should be referencing is B6 and the range of cells I want the code to apply to is A24:B27.

Thanks in advance!!

[pike]

Hi _Ryan_
Is this what you mean?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim myRange As Range
    On Error Resume Next
    Set myRange = Intersect(Range("A24:B27"), Target)
    If Not myRange Is Nothing And Range("B6").Value > 2 Then
       MsgBox "Invalid amount of" & vbCrLf & _
"outpatient visits", 64, "Access into cell not allowed."
    End If
End Sub

[_Ryan_]

Yes! That is perfect! The only thing I changed was where you had ">2" i changed it to "<2"

I am going to add this code for all the different ranges I have. If I have any more questions about the code, I will let you know!!

Thanks again pike!

[_Ryan_]

Is there a way to make a check box also work with cells being locked? Like if a checkbox is checked, it unlocks a couple other cells? I have checkboxes that if any one of them is checked, the user can move on to the next question. How can I make the following questions unlock and when they are locked, they should be gray?

[_Ryan_]

nvm i think i got it.

[pike]

Hi _Ryan_
Can you please mark your post solved if the answer solved your question?