Auto create Hyperlink to external file

What did you try? Try this modification, although I haven't tested it because the destination is a network drive:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Column = 2 And Target.Value = "Yes" Then
      Target.Hyperlinks.Add Target, "\\TUGALW-FP02\DEPT\VE\Returned\" & Format(Target.Offset(0, -1), "0000") & ".pdf"
      Target.Value = Format(Target.Offset(0, -1), "0000") & ".pdf"
   End If

End Sub