Insert date & time when 5 condition are true

  • Thread starter Thread starter Jenn
  • Start date Start date
J

Jenn

I am trying to create VB code in order for the following to occur:

Cells E3:I3 must all equal "Completed" (dropdown selection using Data
Validation) in order for cell L3 to insert a date and time stamp. Once the
date & time are inserted they MUST remain constant, they cannot change with
recalculations and reopening the spreadsheet.

Any ideas?
 
Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("E3:I3")) Is Nothing Then
If WorksheetFunction.CountIf(Range(UCase("E3:I3")), "COMPLETED") = 5 Then
Range("L3").Value = Now
End If
End If
End Sub

Mike
 
This subroutine does what you want
Sub tryme()
mytest = "Completed"
mycount = WorksheetFunction.CountIf(Range("E3:I3"), mytest)
If mycount = 5 And Range("L3") = "" Then
DateStamp = Date + Time
Range("L3") = DateStamp
End If
End Sub

But if you want it to run automatically whenever the fives cell have the
correct text, then you need to but this as part of a worksheet change macro.
For details see:
http://www.ozgrid.com/VBA/run-macros-change.htm
best wishes
 
My further question is, how do I apply this logic to work beyond just row 3?
This same prinicipal is needed in numerous cells below that.

Here is the code that did the job:
Private Sub Worksheet_Change(ByVal Target As Range)
mytest = "Complete"
mycount = WorksheetFunction.CountIf(Range("E3:I3"), mytest)
If mycount = 5 And Range("L3") = "" Then
DateStamp = Date + Time
Range("L3") = DateStamp
End If
End Sub
 
Jenn,

This will now make it work for all rows

Private Sub Worksheet_Change(ByVal Target As Range)
mytest = "Complete"
If Target.Column < 5 Or Target.Column > 9 Then Exit Sub
mycount = WorksheetFunction.CountIf(Range("E" & Target.Row & ":I" &
Target.Row), mytest)
If mycount = 5 And Range("L" & Target.Row) = "" Then
DateStamp = Date + Time
Application.EnableEvents = False
Range("L" & Target.Row) = DateStamp
Application.EnableEvents = True
End If

End Sub

Mike
 
Back
Top