CODEWITHSUNDEEP
excelvbadateexcel-formulaformula
juiceb0xk
juiceb0xk

Reputation: 969

How do I update the date in a cell ONLY if a certain cell is modified

I have it set up right now so when the cell data changes, it updates the date. When refreshing the workbook, it also updates the date, even if I haven't modified the cell contents.

I only want to update the date if I modify the cell contents. I am pulling my data from a CSV file so I constantly have to refresh every now and again. How do I get around this so that it only shows the date upon which a cell is truly modified?

I have a public function set up like this at the moment, but it does not work.

Public Function worksheetChange(ByVal Target As Range)

If Not Application.Intersect(Target, [D4]) Is Nothing Then [L4] = Date

End Function

Even when I refresh the worksheet and I haven't modified the cell, it still updates the date, how do I get around this?

EDIT: I've come up with this type of VBA code, but I'm not sure how to get it working. 

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, [D4]) Is Nothing Then
If Not ActiveWorkbook.RefreshAll Then [L4] = Date

End Sub

In hindsight, this code means that if the application doesn't intersect then take D4 and also, if the workbook doesn't refresh, update L4 as todays date.

Thank you.

Upvotes: 0

Views: 1926

Answers (1)

Mukul Varshney
Mukul Varshney

Reputation: 3141

Refer the link

Dim oldValue

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
  oldValue = Target.Worksheet.Range("D4").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("D4")) Is Nothing Then
      If oldValue <> Target.Worksheet.Range("D4").Value Then
        Target.Worksheet.Range("L4").Value = Date
      End If
    End If
End Sub

Upvotes: 1

Related Questions