It looks like this line is what's causing your problems

For Each Target In Selection

To avoid confusion, I suggest declaring another range variable and construct
your code like this:

Dim c as Range
For Each c In Target.Range
If SheetExists(c.Value) Then
MsgBox "Sheet Name " & c.Value & " already used!",
vbCritical, "Duplicate Sheet Name Error"
Application.EnableEvents = False
Application.Undo
Else
Select Case c.Column
Case 2
Sheet2.Name = c.Value
Case 3
Sheet3.Name = c.Value