Locking a range of cells without protecting the worksheet

[Rab_marr]

Good afternoon all,

I have working on different ways to lock cells without protecting the worksheet. I have found the following code to perform someway to what I am looking for:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Column = 6 Then
If Target.Row = 3 Or Target.Row = 5 Then
Beep
Cells(Target.Row, Target.Column).Offset(0, 1).Select
End If
End If

End Sub

Unfortunately my VBA knowledge is extremely limited and do not know how to extend the code from applying to a single cell to a range of cells.

Is there anyone who can help.

thanks

Rab

 

[bugmonsta]

Hi,

This sample shows that the Range F2 to G5 will force the code to kick in and move the cursor to H on the relevant row.

Hope this helps

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Application.Intersect(Target, Range("F2:G5")) Is Nothing Then
 Cells(Target.Row, Target.Column).Offset(0, 1).Select
 End If
 End Sub

 

[bugmonsta]

And if you want to do multiple different ranges (someone in the office has just asked me after I showed them this) then in the sample below I have now added in B3 to C3 as an added range:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Application.Intersect(Target, Range("F2:G5")) Is Nothing Or _
 Not Application.Intersect(Target, Range("B3:C3")) Is Nothing Then
 Cells(Target.Row, Target.Column).Offset(0, 1).Select
 End If
 End Sub

 

[Rab_marr]

Bugmonsta,

That's great thanks I will give it a try.

Rab