I'm using Excel 2003. I was given the following formual but it has a problem
which I'll explain in a second:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C6"), rng) Is Nothing Then
If Target.Value < "" Then
Range("D6").Value = Target.Value
End If
End If
End If
EndMacro:
End Sub

What SHOULD happen: user selects from a dropdown in cell D4 which then
generates a random item in cell C6, which the value of C6 then gets pasted to
D6. (C6 contains a random formula so in order for the data to remain
constant I need to paste it to D6.)

What ACTUALLY happens: which ever item the user selects from the dropdown in
cell D4 is what is pasted to D6. I'm guessing that the targeting in the
above formula is where this is happening, but I know very little about vba.