I want to create code to run simple procedure only when the triggering cell
will change. I have tried all suggestions (Worksheet_Change event) found on
this newsgroup and nothing works for me. Please help.

I want the code:

For i = 3 To 14
Cells(4, i) = Cells(4, i - 1) + 7
Next i

to be executed only when the value in the cell B4 will be changed by user -
new value entered.

Thanks for help.

Tony

"Tony" wrote in message
...
I want to create code to run simple procedure only when the triggering cell
will change. I have tried all suggestions (Worksheet_Change event) found
on
this newsgroup and nothing works for me. Please help.

I want the code:

For i = 3 To 14
Cells(4, i) = Cells(4, i - 1) + 7
Next i

to be executed only when the value in the cell B4 will be changed by
user -
new value entered.

Thanks for help.

Tony

Tony,

Would nesting a if then else condition in a do while loop be possible for
your code? For example:

i=0
do while i 3
if i 14 then
' "Exit 'cause you're done"
else
Cells(4, i) = Cells(4, i - 1) + 7
i=i+1
loop

right click sheet tabview codeleft window select worksheetright window
select worksheet_change

if target.address < "$B$4" then exit sub
your code


--
Don Guillett
SalesAid Software

"Tony" wrote in message
...
I want to create code to run simple procedure only when the triggering
cell
will change. I have tried all suggestions (Worksheet_Change event) found
on
this newsgroup and nothing works for me. Please help.

I want the code:

For i = 3 To 14
Cells(4, i) = Cells(4, i - 1) + 7
Next i

to be executed only when the value in the cell B4 will be changed by
user -
new value entered.

Thanks for help.

Tony

right click on the sheet tab, and select view code. paste in code like
this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ErrHandler
If Target.Address = "$B$4" Then
Application.EnableEvents = False
For i = 3 To 14
Cells(4, i) = Cells(4, i - 1) + 7
Next i
End If
ErrHandler:
Application.EnableEvents = True
End Sub


this assumes the value of B4 will change because the cell is edited or it is
changed programmatically or it is updated by DDE.

If B4 contains a formula and that is what you want to react to, then you
would need to put the code in the calculate event.

However, why not just put the formula

=B4+7 in C4 and drag fill to the right.

--
Regards,
Tom Ogilvy


"Tony" wrote in message
...
I want to create code to run simple procedure only when the triggering
cell
will change. I have tried all suggestions (Worksheet_Change event) found
on
this newsgroup and nothing works for me. Please help.

I want the code:

For i = 3 To 14
Cells(4, i) = Cells(4, i - 1) + 7
Next i

to be executed only when the value in the cell B4 will be changed by
user -
new value entered.

Thanks for help.

Tony

I completely agree with Tom, and I might add that it's a good practice to use
named ranges; they can make your life easier when you make changes to your
workbook.

"Tom Ogilvy" wrote:

right click on the sheet tab, and select view code. paste in code like
this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ErrHandler
If Target.Address = "$B$4" Then
Application.EnableEvents = False
For i = 3 To 14
Cells(4, i) = Cells(4, i - 1) + 7
Next i
End If
ErrHandler:
Application.EnableEvents = True
End Sub


this assumes the value of B4 will change because the cell is edited or it is
changed programmatically or it is updated by DDE.

If B4 contains a formula and that is what you want to react to, then you
would need to put the code in the calculate event.

However, why not just put the formula

=B4+7 in C4 and drag fill to the right.

--
Regards,
Tom Ogilvy


"Tony" wrote in message
...
I want to create code to run simple procedure only when the triggering
cell
will change. I have tried all suggestions (Worksheet_Change event) found
on
this newsgroup and nothing works for me. Please help.

I want the code:

For i = 3 To 14
Cells(4, i) = Cells(4, i - 1) + 7
Next i

to be executed only when the value in the cell B4 will be changed by
user -
new value entered.

Thanks for help.

Tony