Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Target.Offset(0, 1).Resize(1, 4)
.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R1,0), FALSE)"
.Value = .Value
End With
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"Barb Reinhardt" wrote in message
...
I have this now. In A1:A20 I have a selection list that's been defined by a
named range. Whenever I select one of the cells, the macro appears to run.
I want it to run only if the cell values change. What do I need to modify.
Also, I want to copy/paste :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub

'Range("...") identifies that range that will be checked for a change
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1).Resize(1, 4).FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R1,0), FALSE)"
Application.EnableEvents = True
'need to select cells that had VLOOKUP in them and copy/paste special
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
End Sub


"Bernie Deitrick" wrote:

Barb,

Perhaps:

Application.EnableEvents=False
Target.Offset(0,1).Resize(1,4)..FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"
Application.EnableEvents=True

HTH,
Bernie
MS Excel MVP


"Barb Reinhardt" wrote in message
...
I am using a worksheet selectionchange event and want to enter some values in
adjacent cells based on the entry in the selected cells. I know what I want
to enter, but need to know the code to select the offset cells. The selected
cells are in column 1. What would I need to change below to get this to be
more elegant?

I have this:

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"

Thanks,
Barb Reinhardt