Modify code
Hi,
I have applied the following code to my worksheet: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:A20"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub The code works but does not quite satisfy my requirements. All the cells in the range A1:A20 have dropdown boxes with the choice of B,F,J,N. When B or F is selected the value "Now" sould be entered in the offset cells and if J or N is selected the offset cells shall be cleared. Please assist with this problem. Best regards |
Modify code
Replace:
If IsEmpty(.Value) Then with: If IsEmpty(.Value) Or .Value = "J" OR .Value = "N" Then -- Gary''s Student - gsnu201001 "bigmaas" wrote: Hi, I have applied the following code to my worksheet: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:A20"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub The code works but does not quite satisfy my requirements. All the cells in the range A1:A20 have dropdown boxes with the choice of B,F,J,N. When B or F is selected the value "Now" sould be entered in the offset cells and if J or N is selected the offset cells shall be cleared. Please assist with this problem. Best regards |
Modify code
Perfect - many thanks
"Gary''s Student" wrote: Replace: If IsEmpty(.Value) Then with: If IsEmpty(.Value) Or .Value = "J" OR .Value = "N" Then -- Gary''s Student - gsnu201001 "bigmaas" wrote: Hi, I have applied the following code to my worksheet: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:A20"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub The code works but does not quite satisfy my requirements. All the cells in the range A1:A20 have dropdown boxes with the choice of B,F,J,N. When B or F is selected the value "Now" sould be entered in the offset cells and if J or N is selected the offset cells shall be cleared. Please assist with this problem. Best regards |
All times are GMT +1. The time now is 03:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com