Locking a range of cells without protecting the worksheet

Rab_marr

New Member
Joined
Apr 12, 2016
Messages
24
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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 :)

Code:
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
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top