Conditional date stamp?

[CJ-22]

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.

[JE McGimpsey]

Sure:

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 <[email protected]>,
CJ-22 <[email protected]> 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?

[CJ-22]

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 also delete?

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

Thanks again.

[JE McGimpsey]

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 <[email protected]>,
CJ-22 <[email protected]> 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.

[CJ-22]

Outstanding! That works great. Thanks so much JE McGimpsey.

[Bcastel]

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.
>
>