Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
Conditional Time-Stamp | Excel Discussion (Misc queries) | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
conditional formatting & time stamp | Excel Discussion (Misc queries) | |||
Date stamp spreadsheet in excel to remind me of completion date | Excel Worksheet Functions |