<FWIW
I prefer to put processing code in a standard module that can be
'called' from any sheet that may need to use that same code. In this
case I'd probably do something like...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C4:C9")) Is Nothing _
Or Target.Count 1 Then Exit Sub
Call XferSheetData(Me, Target, Sheets(Target.Row - 2))
End Sub


In a standard module:

Sub XferSheetData(wksSrc As Worksheet, _
rngSrc As Range, wksTgt As Worksheet)
Dim sAddr$

Select Case UCase(wksSrc.rngSrc.Value)
Case "E": sAddr = "C5"
Case "G": sAddr = "D5"
Case "S": sAddr = "E5"
Case "N": sAddr = "F5"
End Select

If sAddr < "" Then wksSrc.rngSrc.Offset(0, 1) = wksTgt.Range(sAddr)
End Sub

I might not include the actual transfer code (last executable line
above) in this procedure if I configure it as a function to return the
ref cell address. (Of course, you do know a simple LOOKUP function
would get the job done without need for VBA! Right?<g)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion