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

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

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

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