Excel VBA Event Handlers

I always recommend this when using Worksheet_Change

1. You do not need the sheet name. It is understood that the code is to be run on current sheet unless you are trying to use another sheet row as a reference as correctly mentioned by brettdj in the comments below.

2. Whenever you are working with Worksheet_Change event. Always switch Off events if you are writing data to the cell. This is required so that the code doesn't go into a possible endless loop

3. Whenever you are switching off events, use error handling else if you get an error, the code will not run the next time.

Here is an example

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoa Application.EnableEvents = False ' '~~> Rest of the code ' LetsContinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub 

So using the above, your code becomes (UNTESTED)

Private Sub Worksheet_Change(ByVal Target As Range) Dim rowCount As Long On Error GoTo Whoa Application.EnableEvents = False With ActiveSheet rowCount = .Cells(.Rows.Count, "A").End(xlUp).Row End With If Not Intersect(Target, Range("M3:M" & rowCount)) Is Nothing Then MsgBox ("Hi") ElseIf Not Intersect(Target, Range("T3:T" & rowCount)) Is Nothing Then MsgBox ("Hi") ElseIf Not Intersect(Target, Range("X3:X" & rowCount)) Is Nothing Then MsgBox ("Hi") ElseIf Not Intersect(Target, Range("AB3:AB" & rowCount)) Is Nothing Then MsgBox ("Hi") ElseIf Not Intersect(Target, Range("AI3:AI" & rowCount)) Is Nothing Then MsgBox ("Hi") End If LetsContinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub 

EDIT:

Regarding your 2nd question. As I mentioned in the comment above, you can use a Public variable to check if the worksheet change event is being caused by the workbook open.

Place this code in a module.

Public WasWbOpened As Boolean 

Place this code in the workbook code area

Option Explicit Private Sub Workbook_Open() WasWbOpened = True ' '~~> Rest of the code ' WasWbOpened = False End Sub 

And change your worksheet change event to

Private Sub Worksheet_Change(ByVal Target As Range) If WasWbOpened = True Then Exit Sub Dim rowCount As Long On Error GoTo Whoa Application.EnableEvents = False With ActiveSheet rowCount = .Cells(.Rows.Count, "A").End(xlUp).Row End With If Not Intersect(Target, Range("M3:M" & rowCount)) Is Nothing Then MsgBox ("Hi") ElseIf Not Intersect(Target, Range("T3:T" & rowCount)) Is Nothing Then MsgBox ("Hi") ElseIf Not Intersect(Target, Range("X3:X" & rowCount)) Is Nothing Then MsgBox ("Hi") ElseIf Not Intersect(Target, Range("AB3:AB" & rowCount)) Is Nothing Then MsgBox ("Hi") ElseIf Not Intersect(Target, Range("AI3:AI" & rowCount)) Is Nothing Then MsgBox ("Hi") End If LetsContinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub 

You have several issues

1. As per Sid's comment you should use rowCount = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
2. You should be using Set ws = Sheets("datasheet") not Set Worksheet = "datasheet"
3. Your Else statements are causing errors as they are orphaned. If you want to exit on the first "Hi" rather than continuing to test you should try something like this

Sid has covered your other point. You can use a Boolean variable for this

updated code

this tests the intersect on columns M, T, X, AB and AI from row 3 to row rowCount in a single line

Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim rowCount As Long Set ws = Sheets("datasheet") rowCount = ws.Cells(Rows.Count, "A").End(xlUp).Row If Not Intersect(Target, Range("M3:M" & CStr(rowCount) & ",T3:T" & CStr(rowCount) & ",X3:X" & CStr(rowCount) & ",AB3:AB" & CStr(rowCount) & ",AI3:AI" & CStr(rowCount))) Is Nothing Then MsgBox ("Hi") End Sub