VB Script for a time stamp

[steve2115]

Need help with a vb script to put a time stamp on excel sheet based off criteria being reached

Cell C5 formula is =SUM($E$9:$E$60)/C1 This is a calculated field. It determines the % complete.

I would like to put a 'time stamp' in Cell F6 when Cell C5 is > or = to 90%.
However, if cell C5 hits the 90% mark but it was a user entry error. I would like the time stamp to be removed if Cell C5 is < 90% after corrected entry is made.

Any help would be greatly appreciated.

Thanks in advance.

 

[par60056]

Welcome to the forum. You will find much knowledge here.

Try this worksheet function.

Private Sub Worksheet_calculate()
 
Application.EnableEvents = False
If Cells(5, 3) >= 0.9 And Cells(6, 6) = "" Then
    Cells(6, 6) = Now
ElseIf Cells(5, 3) < 0.9 Then
    Cells(6, 6) = ""
End If
Application.EnableEvents = True
End Sub

 

[chandramohan_fm]

We can use this also

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("C5")) Is Nothing Then
Application.EnableEvents = False
If Target.Value >= 90 Then

Range("F6").Value = Now()
Else
Range("F6").ClearContents
End If
Application.EnableEvents = True
End If


End Sub

 

[chandramohan_fm]

please try this not above
Private Sub Worksheet_calculate(ByVal Target As Range)


If Not Intersect(Target, Range("C5")) Is Nothing Then
Application.EnableEvents = False
If Target.Value >= 90 Then

Range("F6").Value = Now()
Else
Range("F6").ClearContents
End If
Application.EnableEvents = True
End If


End Sub

 

[chandramohan_fm]

sory it will not work

 

[par60056]

C5 is not changing. C5 is a formula. (I tried this first)

Also 90% is .9 You need to know what the actual numbers being summed is to know if you want to test against 90 or .9

 

[par60056]

The calculate sent does not get a target.

 

[steve2115]

par60056 Thanks script worked perfectly.

Regards,
Steve

 

[par60056]

par60056 Thanks script worked perfectly.

Thank you for the feed back.