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

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

"Jenn" wrote:

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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jenn" wrote in message
...
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?
--
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

--
Jenn


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jenn" wrote in message
...
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?
--
Jenn

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

"Jenn" wrote:

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


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jenn" wrote in message
...
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?
--
Jenn