Delete date if cells

[Pinokkio]

Hi,

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

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.

 

[Joe4]

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.

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]

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]

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]

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]

Basically, you just copy the exact some block of code and change the cell references, i.e.

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]

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