To correct your code
Sub FindPSWD() ' This sub finds the password associated with the code entered by the user
Dim PSWD_Code
' Identify the cell that the Password Code is to be entered
PSWD_Code = Range("c3").Value
On Error Resume Next
Range("D3").Value = Application.WorksheetFunction.VLookup(PSWD_Code, Range("F2:G10000"), 2, False)
If Err.Number <> 0 Then MsgBox "Not Found"
' Move the cursor back to C3
Range("C3").Select
End Sub
To simplify use
Sub FindPW(Target) Dim PW As Range
Set PW = Sheet1.Columns(6).Find(Target)
If Not PW Is Nothing Then [D3] = PW.Offset(, 1)
Range("C3").Select
End Sub
To "automate", put this in the worksheet module. it will run when code is entered in C3
Private Sub Worksheet_Change(ByVal Target As Range)
Dim PW As Range
If Target.Address <> "$C$3" Then Exit Sub
Application.EnableEvents = False
Set PW = Sheet1.Columns(6).Find(Target)
If Not PW Is Nothing Then
[D3] = PW.Offset(, 1)
Else
MsgBox "Not Found"
End If
Range("C3").Select
Application.EnableEvents = True
End Sub