Event to add a date if "X" is entered in a cell
How do I modify this code to put a date in only if a capital x exists in the
adjacent cell? Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("c2:c50000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "mm/dd/yyyy" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub |
Event to add a date if "X" is entered in a cell
Hi,
Try this Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("c2:c50000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else If UCase(.Value) = "X" Then With .Offset(0, 1) .NumberFormat = "mm/dd/yyyy" .Value = Date End With End If End If Application.EnableEvents = True End If End With End Sub Mike "JOSEPH WEBER" wrote: How do I modify this code to put a date in only if a capital x exists in the adjacent cell? Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("c2:c50000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "mm/dd/yyyy" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub |
Event to add a date if "X" is entered in a cell
I misunderstood your requirement, try this instead
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("c2:c50000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else If .Value = "X" Then With .Offset(0, 1) .NumberFormat = "mm/dd/yyyy" .Value = Date End With End If End If Application.EnableEvents = True End If End With End Sub Mike "Mike H" wrote: Hi, Try this Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("c2:c50000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else If UCase(.Value) = "X" Then With .Offset(0, 1) .NumberFormat = "mm/dd/yyyy" .Value = Date End With End If End If Application.EnableEvents = True End If End With End Sub Mike "JOSEPH WEBER" wrote: How do I modify this code to put a date in only if a capital x exists in the adjacent cell? Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("c2:c50000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "mm/dd/yyyy" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub |
Event to add a date if "X" is entered in a cell
if you're not too worried about row number then
Private Sub Worksheet_Change(ByVal Target As Excel.Range) if Target.Column = 3 and Target="X" then target.offset(,1).Resize(1,1)=Date End Sub ....is nice and simple you might want to include a .Count check like Mike did, if there's a possibility of multiple cell entry "JOSEPH WEBER" wrote in message ... How do I modify this code to put a date in only if a capital x exists in the adjacent cell? Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("c2:c50000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "mm/dd/yyyy" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub |
All times are GMT +1. The time now is 01:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com