VBA code to protect cell, (more than one cell?)

[jimjaix]

Hi this code is from Tom (I think)

This code protect A1 cell, I want to know how I can protect let's say A1 to A70 and B50 to B70 without doing this code for every single cell.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 

  If Target.Address <> "$A$1" Then Exit Sub 

   Application.EnableEvents = False 

    MsgBox ("Hey, leave me alone!")

    Application.Undo 

  Application.EnableEvents = True 

End Sub

 

[nbrcrunch]

Elementary, dear Watson. For example, If you had thought to try recording your multi-selection, you would have discovered that the syntax is:

(Say I wanted to select A1, C4 and F5...)

Range("A1,C4,F5").Select

You can also do Ranges of cells:

Range("A1:A10,C4:C40,F5").Select

Then follow that with a With Selection clause.

 

[jimjaix]

I know those range selection, but I just don't know how to put them into that code, can you do it? I am not familar with VBA.

 

[Tom Urtis]

Responding from PM:



Change
If Target.Address <> "$A$1" Then Exit Sub


To
If Intersect(Target, Range("A1:A70,B50:B70")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

 

[jimjaix]

Very sweet, thanks a lot Tom

Responding from PM:



Change
If Target.Address <> "$A$1" Then Exit Sub


To
If Intersect(Target, Range("A1:A70,B50:B70")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

 

[jimjaix]

nvm...