First i have to say thanks for all the help as you have been
lifesaver

this does work but only when cell b4 is changed by user edit so when i
is the result of a calculation it only updates column a.



I cant see why this is

any ideas?

Private prev As Variant


Private Sub Worksheet_Calculate()
Static init As Boolean
Dim v As Variant

Application.EnableEvents = False
On Error GoTo CleanUp

v = Me.Range("E4").Value

If init And v < prev Then
Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0).Value = v

prev = v

ElseIf Not init Then
init = True
prev = Range("E4").Value

End If

CleanUp:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$E$4" Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanUp

With Sheets("sheet17")
x = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(x, 1) = Target
.Cells(x, 2) = Range("F4")
.Cells(x, 3) = Time
End With

CleanUp:
Application.EnableEvents = True
End Sub




--
Message posted from http://www.ExcelForum.com