Insert date & time when 5 condition are true

[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?

 

[Mike H]

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

 

[Bernard Liengme]

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

 

[Jenn]

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

 

[Mike H]

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