Conditional date stamp?
 

I am looking for an event macro that will place a date in column B onl
when the letter "R" is placed in column A. Is this possible?

Thanks for any replies

--
CJ-2
-----------------------------------------------------------------------
CJ-22's Profile: http://www.excelforum.com/member.php...fo&userid=1755
View this thread: http://www.excelforum.com/showthread.php?threadid=52508

Conditional date stamp?
 

Su

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If .Column = 1 Then
If .Value = "R" Then
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy"
Application.EnableEvents = False
.Value = Date
Application.EnableEvents = True
End With
End If
End If
End With
End Sub


If you want to include lowercase r's, substitute the line

If UCase(.Value) = "R" Then

In article ,
CJ-22 wrote:

I am looking for an event macro that will place a date in column B only
when the letter "R" is placed in column A. Is this possible?

Conditional date stamp?
 

Thanks JE McGimpsey, it works great. I have two additional questions.

1) Can this be modified so that if I delete the "R" the date will als
delete?

2) Can this be modified to work in different columns if needed?

Thanks again

--
CJ-2
-----------------------------------------------------------------------
CJ-22's Profile: http://www.excelforum.com/member.php...fo&userid=1755
View this thread: http://www.excelforum.com/showthread.php?threadid=52508

Conditional date stamp?
 

1)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
If .Value = "R" Then
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
ElseIf Len(.Text) = 0 Then
.Offset(0, 1).ClearContents
End If
Application.EnableEvents = True
End If
End With
End Sub

2) Change "A:A" to your desired columns, e.g.:

If Not Intersect(.Cells, Range("J:J")) Is Nothing Then

or

If Not Intersect(.Cells, Range("A:A,J:J")) Is Nothing Then

In article ,
CJ-22 wrote:

1) Can this be modified so that if I delete the "R" the date will also
delete?

2) Can this be modified to work in different columns if needed?

Thanks again.

Conditional date stamp?
 

Outstanding! That works great. Thanks so much JE McGimpsey

--
CJ-2
-----------------------------------------------------------------------
CJ-22's Profile: http://www.excelforum.com/member.php...fo&userid=1755
View this thread: http://www.excelforum.com/showthread.php?threadid=52508

Conditional date stamp?
 

Would there be a way to add a date stamp to a saved file through a
script? Such as <worksheetname_<todaysdate.xls?

CJ-22 wrote:
I am looking for an event macro that will place a date in column B only
when the letter "R" is placed in column A. Is this possible?

Thanks for any replies.