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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
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
54,456
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
54,456
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top