You should not open a new thread for
an on-going topic, especially in view of
your unsolicted testimonial:
This is because it is inefficient in that it
causes an unnecessary duplication of the
time and effort of those who seek to assist
you; it is discourteous to those who may
have already assisted you and, finally, it
substantially reduces the prospects of an
optimal response in that one respondent
may well not see the suggestions of another.
That said, try the following adaptation of
your existing code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Range
Dim r As Range
Dim v As Variant
Const sStr As String = "SubFile"
Const sStr2 As String = "SubFiles"
Set t = Target
Set r = Range("N:N")
If Intersect(t, r) Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
v = t.Value
If v = "" Then
t.Offset(0, 1).Value = ""
If InStr(1, v, ",", vbTextCompare) > 0 _
Or InStr(1, v, "&", vbTextCompare) > 0 Then
t.Offset(0, 1).Value = sStr2
t.Offset(0, 1).Value = sStr
Application.EnableEvents = True