Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete Macro
Can anyone help with a macro to delete the contents of a single cell that
contains a date, when a second cell is changed with a new date. Have tried using the Worksheet_Change event without any success. My file has a manually entered date in cell E3 which triggers Conditional Formatting. When an updated date for an annual report is entered in cell G3, I need the date in cell E3 to be deleted. This also needs to function for all the cells in column G downwards. Thanks In Advance Saruman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete Macro
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in any cell in Col G On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 7 Then n = Target.Row If Excel.Range("G" & n).Value < "" Then Excel.Range("E" & n).ClearContents 'or Excel.Range("E3").ClearContents End If End If enditall: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste into that module. Gord Dibben MS Excel MVP On Wed, 12 Jul 2006 21:32:58 +0100, "Saruman" wrote: Can anyone help with a macro to delete the contents of a single cell that contains a date, when a second cell is changed with a new date. Have tried using the Worksheet_Change event without any success. My file has a manually entered date in cell E3 which triggers Conditional Formatting. When an updated date for an annual report is entered in cell G3, I need the date in cell E3 to be deleted. This also needs to function for all the cells in column G downwards. Thanks In Advance Saruman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete Macro
Thank you for a very quick reply.
Works awesome. Regards Saruman "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in any cell in Col G On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 7 Then n = Target.Row If Excel.Range("G" & n).Value < "" Then Excel.Range("E" & n).ClearContents 'or Excel.Range("E3").ClearContents End If End If enditall: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste into that module. Gord Dibben MS Excel MVP On Wed, 12 Jul 2006 21:32:58 +0100, "Saruman" wrote: Can anyone help with a macro to delete the contents of a single cell that contains a date, when a second cell is changed with a new date. Have tried using the Worksheet_Change event without any success. My file has a manually entered date in cell E3 which triggers Conditional Formatting. When an updated date for an annual report is entered in cell G3, I need the date in cell E3 to be deleted. This also needs to function for all the cells in column G downwards. Thanks In Advance Saruman |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete Macro
Thanks for the feedback.
Gord On Wed, 12 Jul 2006 22:22:12 +0100, "Saruman" wrote: Thank you for a very quick reply. Works awesome. Regards Saruman "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in any cell in Col G On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 7 Then n = Target.Row If Excel.Range("G" & n).Value < "" Then Excel.Range("E" & n).ClearContents 'or Excel.Range("E3").ClearContents End If End If enditall: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste into that module. Gord Dibben MS Excel MVP On Wed, 12 Jul 2006 21:32:58 +0100, "Saruman" wrote: Can anyone help with a macro to delete the contents of a single cell that contains a date, when a second cell is changed with a new date. Have tried using the Worksheet_Change event without any success. My file has a manually entered date in cell E3 which triggers Conditional Formatting. When an updated date for an annual report is entered in cell G3, I need the date in cell E3 to be deleted. This also needs to function for all the cells in column G downwards. Thanks In Advance Saruman Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete picture with macro | Excel Discussion (Misc queries) | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Error when trying to delete a macro... | Excel Discussion (Misc queries) | |||
Can't find macro in an Excel file to delete it | Excel Discussion (Misc queries) | |||
Macro data selection line delete | Excel Discussion (Misc queries) |