=now()

[Trevor3007]

hi,

is it possible to use =now() so it does not change the date on all previous entries to the current day?

Example

insert text into cell A2,and cell B2 has the =now() and returns 28/3/19

I reopen the file another day ( ie 3/4/19) and now find that the text I entered previously in cell A2, cell B2 now has the date of 3/4/19.


Can this be resolved to retain the original date ?


KR
Trevor3007

 

[Scott T]

This will clear the date if the cell in column B is blank.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
    If Target = "" Then Target.Offset(0, -1).ClearContents
    Target.Offset(0, -1) = Date
End If
Application.EnableEvents = True

End Sub

 

[Trevor3007]

hi & thank you fo your help,

I already have this VB in place:-

Private Sub Worksheet_Change(ByVal Target As Range)   
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("z4:z100")) Is Nothing Then


        Application.EnableEvents = False


        Target = UCase(Target)


        Application.EnableEvents = True


    End If
      


    On Error GoTo 0




If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("d2:d2000")) Is Nothing Then


        Application.EnableEvents = False


        Target = StrConv(Target, vbProperCase)


        Application.EnableEvents = True


    End If


    On Error GoTo 0
    
    
    
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("B2:C1000")) Is Nothing Then


        Application.EnableEvents = False


        Target = StrConv(Target, vbProperCase)


        Application.EnableEvents = True


    End If


    On Error GoTo 0
    
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("g2:g1000")) Is Nothing Then


        Application.EnableEvents = False


        Target = StrConv(Target, vbProperCase)


        Application.EnableEvents = True


    End If


    On Error GoTo 0
    
    
 
     
     
     
     
     If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("d3000:d5000")) Is Nothing Then


        Application.EnableEvents = False


        Target = LCase(Target)


        Application.EnableEvents = True


    End If
      


    On Error GoTo 0
    
    
    On Error Resume Next


    If Not Intersect(Target, Range("i2:i200")) Is Nothing Then


        Application.EnableEvents = False


        Target = LCase(Target)


        Application.EnableEvents = True


    End If
      


    On Error GoTo 0
    
    
    
    
    
   If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("x4:x200")) Is Nothing Then


        Application.EnableEvents = False


        Target = UCase(Target)


        Application.EnableEvents = True


    End If
      


    On Error GoTo 0
   
   
   
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("at4:aF200")) Is Nothing Then


        Application.EnableEvents = False


        Target = LCase(Target)


        Application.EnableEvents = True


    End If
      


    On Error GoTo 0
    
    




End Sub

I inserted you code at the end & removed the additional 'end sub' and Private Sub Worksheet_Change(ByVal Target As Range) . When I remove the data in col B, the date remains?

Any suggs?

KR
Trevor3007

 

[Scott T]

Try

Application.EnableEvents = False
If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
    Target.Offset(0, -1) = Date
    If Target = "" Then Target.Offset(0, -1).ClearContents
End If
Application.EnableEvents = True

 

[Trevor3007]

FANTASTIC....

Works a treat!!!! Thank you .

KR
Trevor3007