Delete date if cells

Pinokkio

New Member
Joined
Sep 23, 2009
Messages
4
Hi,

Whit this macro I can autom. set date in column A.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([B1:C100], Target) Is Nothing Then
    Application.EnableEvents = False
    Cells(Target.Row, 1) = Now
    Application.EnableEvents = True
 End If
End Sub

Can someone help me if I delete in column B & C the data the date in column A most delete.

'This most also with the other colums, E=date and F and G are input?


thanks for helping me,

P.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Can someone help me if I delete in column B & C the data the date in column A most delete.
This variation of the code should do that.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
'   Only run if a single cell in range B1:C100 is updated
    If (Not Intersect([B1:C100], Target) Is Nothing) And (Target.Count = 1) Then
        Application.EnableEvents = False
'       If an entry is made, update date value in column A
        If Len(Target) > 0 Then
            Cells(Target.Row, "A") = Now
'       If an entry is removed, remove date value in column A
        Else
            Cells(Target.Row, "A").ClearContents
        End If
        Application.EnableEvents = True
    End If
    
End Sub
'This most also with the other colums, E=date and F and G are input?
Its not quite clear what you are asking here. Can you try asking that again?
 

Pinokkio

New Member
Joined
Sep 23, 2009
Messages
4
Thanks for helping me out.

If I put data in the cells B7 or C7 appears in column A the date.
So far so good.
If I delete the data in B7 or C7 then the date must disappear.
Sorry for my English.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
Office Version
  1. 365
Platform
  1. Windows
If I put data in the cells B7 or C7 appears in column A the date.
So far so good.
If I delete the data in B7 or C7 then the date must disappear.
That is exactly what the code I posted should do. Note though, that it only works if you delete values one cell at a time (the code will not run if you highlight a whole range of cells at once and delete).

What I was questioning was your last part. I am not sure what you are asking in regards to columns E, F, and G.
 

Pinokkio

New Member
Joined
Sep 23, 2009
Messages
4

ADVERTISEMENT

Sorry I had the wrong sheet where I had put the code?
Now it works.

What I was questioning was your last part. I am not sure what you are asking in regards to columns E, F, and G.

Now it would be doing the same with the columns E = date and Fand G the data?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
Office Version
  1. 365
Platform
  1. Windows
Basically, you just copy the exact some block of code and change the cell references, i.e.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
'   Exit immediately if more then one cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if a single cell in range B1:C100 is updated
    If Not Intersect([B1:C100], Target) Is Nothing Then
        Application.EnableEvents = False
'       If an entry is made, update date value in column A
        If Len(Target) > 0 Then
            Cells(Target.Row, "A") = Now
'       If an entry is removed, remove date value in column A
        Else
            Cells(Target.Row, "A").ClearContents
        End If
        Application.EnableEvents = True
    End If
    
'   Only run if a single cell in range E1:F100 is updated
    If Not Intersect([E1:F100], Target) Is Nothing Then
        Application.EnableEvents = False
'       If an entry is made, update date value in column D
        If Len(Target) > 0 Then
            Cells(Target.Row, "D") = Now
'       If an entry is removed, remove date value in column D
        Else
            Cells(Target.Row, "D").ClearContents
        End If
        Application.EnableEvents = True
    End If
        
End Sub
 

Pinokkio

New Member
Joined
Sep 23, 2009
Messages
4
Cells(Target.Row, "D") = Now
' If an entry is removed, remove date value in column D
Else
Cells(Target.Row, "D").ClearContents

have just changed in D E

Now I works fine.

Thanks,

JP
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,947
Members
430,329
Latest member
asmith75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top