I am pretty sure you don't want the sub to execute when Excel opens and
continue until Excel closes. So under what circumstances should it run?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"ToddEZ"
wrote in message
I wrote some code to copy formulas to different cells. I would like the code
to run automatically without having to "run the macro". Below is me code.
Please help.

Sub copyformula()

Application.ScreenUpdating = True

Worksheets("Sheet1").Activate
For Each cell In Range("c1:c1000")
If cell.Value = "Y" Then
cell.Offset(0, 4).Formula = _
cell.Offset(0, 2) + 20

cell.Offset(0, 5).Formula = _
cell.Offset(0, 2) + 26

cell.Offset(0, 6).Formula = _
cell.Offset(0, 2) + 31

End If
Next cell

End Sub

Place the following code in the Sheet1 module ...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo QuitChanging
If Target.Column = 3 Then
Application.EnableEvents = False
If Target.Cells(1, 1).Value = "Y" Then
Call copyformula
End If
End If
QuitChanging:
Application.EnableEvents = True
End Sub
------------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"ToddEZ"
wrote in message
I would like it to copy over the formulas any time someone types a "Y" in
column C.
thanks for your help.

"Jim Cone" wrote:

I am pretty sure you don't want the sub to execute when Excel opens and
continue until Excel closes. So under what circumstances should it run?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"ToddEZ"
wrote in message
I wrote some code to copy formulas to different cells. I would like the code
to run automatically without having to "run the macro". Below is me code.
Please help.

Sub copyformula()

Application.ScreenUpdating = True

Worksheets("Sheet1").Activate
For Each cell In Range("c1:c1000")
If cell.Value = "Y" Then
cell.Offset(0, 4).Formula = _
cell.Offset(0, 2) + 20

cell.Offset(0, 5).Formula = _
cell.Offset(0, 2) + 26

cell.Offset(0, 6).Formula = _
cell.Offset(0, 2) + 31

End If
Next cell

End Sub