Automatically update the date in the cell when changing another cell value (as calculated by the formula)

Question

I have a formula in C2, say =A2+B2. Whenever C2 changes the value (the actual value, not the formula), I want the current date and time to be updated in D2.

I have tried many VBA codes and tricks, and none of them work if the formula is introduced in C2. BUT, if I type the value manually in C2, the date and time is updated as needed. This, of course, is because the real value / is introduced - where the formula remains unchanged, so to speak.

Question: Is it possible to create VBA code (or something else) that updates D2 when the result of the formula in C2 changes?

If possible, I need this to be active for C2: C30 cells (+ D2: D30 for date + time)

Using Excel 2010.

Answer 1

You can fill in the dependent cell (D2) using the User Defined Function (macroeconomic function VBA), which takes a C2-Cell value as an input parameter, returning the current date as an output.

Having C2 as an input parameter for UDF in D2 tells Excel that it needs to reevaluate D2 every time C2 changes (that is, if automatic formula calculation is enabled for the workbook).

EDIT:

Here is the code:

For UDF:

    Public Function UDF_Date(ByVal data) As Date

        UDF_Date = Now()

    End Function

Like the formula in D2:

=UDF_Date(C2)

You will need to provide the D2-Cell date and time format, or it will show a numerical representation of the date value.

, , C2 D2.

: , , , , Excel , D2 reset . D2 C2, - C2. , , UDF, , , UDF.

:

UDF, -, . , :

• UDF , .

• UDF .

• -, . , , , ( + .) , , Excel , .

• , .

• , .

• . .

  Public Function UDF_Date(ByVal inData As Range) As Date
  
      Dim wb As Workbook
      Dim dProps As DocumentProperties
      Dim pValue As DocumentProperty
      Dim pDate As DocumentProperty
      Dim sName As String
      Dim sNameDate As String
  
      Dim bDate As Boolean
      Dim bValue As Boolean
      Dim bChanged As Boolean
  
      bDate = True
      bValue = True
  
      bChanged = False
  
  
      Dim sVal As String
      Dim dDate As Date
  
      sName = inData.Address & "_" & inData.Worksheet.Name
      sNameDate = sName & "_dat"
  
      sVal = CStr(inData.Value)
      dDate = Now()
  
      Set wb = inData.Worksheet.Parent
  
      Set dProps = wb.CustomDocumentProperties
  
  On Error Resume Next
  
      Set pValue = dProps.Item(sName)
  
      If Err.Number <> 0 Then
          bValue = False
          Err.Clear
      End If
  
  On Error GoTo 0
  
      If Not bValue Then
          bChanged = True
          Set pValue = dProps.Add(sName, False, msoPropertyTypeString, sVal)
      Else
          bChanged = pValue.Value <> sVal
          If bChanged Then
              pValue.Value = sVal
          End If
      End If
  
  On Error Resume Next
  
      Set pDate = dProps.Item(sNameDate)
  
      If Err.Number <> 0 Then
          bDate = False
          Err.Clear
      End If
  
  On Error GoTo 0
  
      If Not bDate Then
          Set pDate = dProps.Add(sNameDate, False, msoPropertyTypeDate, dDate)
      End If
  
      If bChanged Then
          pDate.Value = dDate
      Else
          dDate = pDate.Value
      End If
  
  
      UDF_Date = dDate
   End Function
  

Answer 2

.

, , C2: C2, , , .

@Paul S

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim R1 As Range
 Dim R2 As Range
 Dim InRange As Boolean
    Set R1 = Range(Target.Address)
    Set R2 = Range("C2:C20")
    Set InterSectRange = Application.Intersect(R1, R2)

  InRange = Not InterSectRange Is Nothing
     Set InterSectRange = Nothing
   If InRange = True Then
     R1.Offset(0, 1).Value = Now()
   End If
     Set R1 = Nothing
     Set R2 = Nothing
 End Sub

Answer 3

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$C$2" Then

        ActiveSheet.Range("D2").Value = Now()

    End If

End Sub

Answer 4

The easiest way is to add =IF(B3="","Not Allocated",Now())and change the column format to the desired date and time format. But here, if column B is edited, the date and time of the corresponding column that needs updating is automatically updated for all columns, since they do not check the old value. But if it’s normal to get the current time, it can be easily used.