Thanks Dave for your reply and code,
It didn't work at first, so I had a dig aroud in the VB help files and with
a bit of trial and error (mostly error!) managed to come up with this that
works-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
Dim myRng As Range

If Target.Cells.Count 1 Then Exit Sub
myRow = Target.Row - 1

If Application.Intersect(Range("b1:c150"), Target) Is Nothing Then
Exit Sub
End If

Set myRng = Me.Range(Cells(myRow, "D").Offset(1, 0), Cells(myRow,
"X").Offset(1, 0))

'stop the change from firing this event
Application.EnableEvents = False
myRng.Replace what:="L??O???", _
Replacement:=Me.Cells(Target.Row, "a").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

Target.Offset(1, 0).Select
Application.EnableEvents = True

End Sub

I was wondering if you could help me with on further issue?

The whole pont of this code is to replace part of a reference in a DDE link.
The number entered into col b is always the same and by using find and
replace these can be chaged quite easily and the code now works a dream.
The numbers entered into col c are usually sequential, I have tried using a
sum function in each of the cells following the first to add 1 to the number
entered into c5 an typing the first 2 entries then dragging the sequece down
the page but the code does'nt run, presumably because enter has to be hit to
trigger the code!
Is there a way of triggering the code when just the value has been changed?

Many thank in advance

Mark


"Dave Peterson" wrote:

First,

Range("b1:b150", "d1:D150")
is the same as:
range("B1:D150")

If you really wanted just rows 1:150 in column B or D, then use:
range("b1:b150,d1:d150")



Private Sub Worksheet_Change(ByVal Target As Range)
dim myRow as long
dim myRng as range

If Target.Cells.Count 1 Then Exit Sub
myrow = target.row -1

If Application.Intersect(Range("b1:d150"), Target) Is Nothing Then
exit sub
end if

set myrng = me.range(.cells(myrow,"D"),.cells(myrow,"X"))

'stop the change from firing this event
application.enableevents = false
myrng.Replace what:="L??O???", _
Replacement:=me.Cells(target.row, "a").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

Target.Offset(1, 0).Select
application.enableevents =true

End Sub

(Untested, uncompiled.)

Mark Dullingham wrote:

I have the following code in my work book. It does exactly what I want it to
apart from each time a value is changed and the code runs the code scrolls
back through the entire sheet and as the number of rows is getting quite
extensive now this can take some time.
Does anyone have any idea how to stop this from happening.

Many thanks in advance

Mark Dullingham

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("b1:b150", "d1:d150"), Target) Is
Nothing Then
Range("D" & ActiveCell.Offset(-1, 0).Row & ":X" & ActiveCell.Offset(-1,
0).Row).Select
Selection.Replace what:="L??O???", _
Replacement:=Cells(ActiveCell.Row, "a").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Target.Offset(1, 0).Select
End If


End Sub

--

Dave Peterson