run macro if cell value changes

[Maurice]

I'm trying to run the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target
If Range("C65536").End(xlUp).Address = "$C$10" _
Then Range("$C$2").Delete Shift:=xlUp

End If
End Sub

The value of A1 changes automatically (the outcome of a function), because
of this, the above mentioned code doesn't run.
Is it correct to use the 'change event' code, as described below, to make
the above mentioned code recognise that the value of A1 is changed?
If so, how do i intergrate both codes?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "B2" Then
Dim Blank_count As Integer
etc
etc
End If
End Sub


thanks very much for all help!

Maurice
Amsterdam, The Netherlands

 

[David McRitchie]

Hi Maurice,

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'should be part of Change macro
IF .... then
...
ElseIF .... then
...
ElseIF .... then
...
Else 'not needed with what you posted...

End IF
Application.EnableEvents = True 'should be part of Change macro
End Sub

See you VBA Help for IF then Else statement

If your macro terminates without reenabling events
you might need to run a macro like that found in
http://www.mvps.org/dmcritchie/excel/event.htm#problems
Sub Fix_Things()
Application.DisplayAlerts = True
Application.EnableEvents = True 'should be part of Change macro
End Sub ---HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htmSearch

 

[Maurice]

Thanks David, but i am practically new to vba, and i am not sure how to
proceed.