| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting on "Any Text" entered in a cell | Excel Discussion (Misc queries) | |||
| creating a "date selector box" or "pull down box" in a cell | Excel Worksheet Functions | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| bunch of "yes" or "no" entered in row, output 1 if a single yes | Excel Worksheet Functions | |||
| Macro that runs entered value through "low" and "high" range | Excel Programming | |||
Linear Mode
