@littlebethan
Elaborating on the reply from Quadruple Pawn… Procedures that run automatically based on events (such as opening a workbook or changing the value of cells) are called event handlers (also called event procedures, but they are not strictly speaking called macros; but both event handlers and macros are written in VBA). Event handlers for a worksheet are specific to a worksheet, but if you make a copy of a worksheet, a copy of its event handlers is also made.
Some of the events that can happen on/to a worksheet are listed in Worksheet object events. (The instructions at the top of this article for viewing/creating Worksheet event handlers are poorly written: Before you can "Select one of the following events from the Procedure list box" – which is the dropdown list on the upper right; it will probably initially show "(Declarations)" – you may need to select "Worksheet" from the Object dropdown list on the left.) Don't worry if the VBA editor (VBE) creates empty event handlers for each event type that you select; they won't cause problems, and you can remove them later.
You might consider the following version of code, or portions of it, for your event handler. It will make more sense when you read it in the future, e.g., when you have to think about the meaning of "Target.Offset(0, -9)" or columns need to be inserted.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngReferences As Range
Dim rngChangedReferences As Range
Dim rngCell As Range
Dim in4Row As Long
'---- Determine if any of the changed cells was a Reference.
Set rngReferences = Range("M:M")
Set rngChangedReferences = Application.Intersect(Target, rngReferences)
If rngChangedReferences Is Nothing Then
'...none of the changed cells were in the range of interest.
GoTo WkshtChange_Exit
End If
'---- [Quadruple Pawn's code skipped the clearing of Email Address
' if multiple cells were changed at once, but that's not
' necessary. If you also want to skip it, uncomment the
' following code. You could also replace Target with
' rngChangedReferences (because depending on where values were
' being pasted in, maybe only one Reference was changed.]
' If Target.Cells.Count > 1 Then
' Call MsgBox("Normally this worksheet would clear the email" _
' & " addresses, but you entered multiple references" _
' & " at once.", vbExclamation Or vbOKOnly)
' GoTo WkshtChange_Exit
' End If
'---- For each of the changed References, if the new value
' is a non-blank value, clear the Email Address for that
' row.
Application.EnableEvents = False '...so as not to cause _
other events to fire from the following _
possible changes in value
For Each rngCell In rngChangedReferences
If Trim$(rngCell.Value) <> "" Then
in4Row = rngCell.Row
Range("D" & in4Row).Value = ""
End If
Next rngCell
WkshtChange_Exit:
Application.EnableEvents = True
Exit Sub
End Sub
Note also that there can be only one event handler per type of event (there can be only one Worksheet_Change procedure per worksheet). So if your code must respond differently to changes in different columns/rows, the code within the procedure must be written slightly differently than our examples.
Random advice: Always include an "Option Explicit" statement at the top of each code module. It will help you detect misspelled names of variables.
As always, be aware that macro-enabled workbooks (ending in .xlsm, for example) are effective ways of transferring malware. Be wary of opening them from sources you do not trust, even such workbooks posted in this forum. (Quadruple Pawn has been a good source for a long time, but what if someone hacks his account, or my account, or…) So I like to show my VBA code in my posts, whether or not I attach a workbook.
