VBA & Macro Problems

How to Record a Date When an Excel Status Cell Changes

Ayan Masood Oct 10, 2026 869 views

Question details

The user wants to automatically record the current date in specific cells (I3 or J3) when the value of a task status dropdown list (H3) changes, without overwriting previously recorded dates incorrectly.

How to Record a Date When an Excel Status Cell Changes

Product: Excel
Device & OS: not provided
Scenario: Tracking project or task statuses where a date needs to be automatically logged the moment a task is marked as 'With Client' or 'In progress'.
Observed behavior: Currently, dates must be entered manually or using formulas that recalculate daily. The goal is to insert a static timestamp dynamically based on the dropdown selection.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macros are enabled in your application's Trust Center settings to allow the VBA code to run automatically.

Solution 1Recommended

Use a Worksheet_Change VBA Macro

Utilize the Worksheet_Change event in VBA to monitor the specific status cell. When a change is detected, the macro writes a static date into the target cell and disables recursive triggers.

By leveraging the Worksheet_Change event, Excel runs your code automatically whenever a user changes a cell's value. To prevent an endless loop of updates, the code temporarily disables Application.EnableEvents while modifying the date cells.

Open the VBA Editor

Right-click the specific worksheet tab at the bottom of your screen where the dropdown is located, and select 'View Code' from the context menu. This will open the Microsoft Visual Basic for Applications window directly to that sheet's module.

Paste the Macro Code

In the blank code window, paste the following VBA code: Option Explicit Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> Range("H3").Address Then Exit Sub Application.EnableEvents = False Select Case Target.Value Case "With Client" Range("I3").Value = Date Range("J3").ClearContents Case "In progress" Range("J3").Value = Date Range("I3").ClearContents Case "" Range("I3:J3").ClearContents End Select Application.EnableEvents = True End Sub

Test the Automation

Close the VBA editor and return to your worksheet. Change the dropdown value in cell H3 to 'With Client' and verify that the current date appears in I3. Change it to 'In progress' and check that the date moves to J3.

Date Integrity: Using VBA's 'Date' function inserts a static value. Unlike the '=TODAY()' formula, it will not recalculate or change on subsequent days unless the status is manually changed again.

Advanced Spreadsheet Automation

Automate Your Workflow Seamlessly with WPS Spreadsheet

WPS Office offers full support for VBA macros, allowing you to automate tasks like timestamping status changes exactly as you would in Microsoft Excel. It is highly compatible and simple to set up.

1. Open the Developer Tab: Launch WPS Spreadsheet, open your file, and navigate to the 'Developer' tab on the top ribbon.
2. Access the VBA Editor: Click 'Visual Basic' or 'Macros' to launch the integrated VBA editor.
3. Insert the Code: Double-click your active worksheet in the left-hand Project pane and paste the Worksheet_Change code.
4. Save as Macro-Enabled: Go to Menu > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your automation works every time you open the file.

Fully supports VBA macros and Worksheet_Change events.Seamlessly compatible with Microsoft Excel .xlsm and .xlsx formats.Lightweight architecture ensures smooth performance on older devices.Free to use for everyday spreadsheet automation and data tracking.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Worksheet_Change macro not triggering?

Macros might be disabled in your security settings, or Application.EnableEvents was accidentally left as False during a previous error. You can reset it by opening the VBA editor, pressing Ctrl+G to open the Immediate Window, typing 'Application.EnableEvents = True', and pressing Enter.

How can I apply this timestamp macro to an entire column instead of a single cell?

You need to modify the code to check the column rather than a specific cell address. Replace 'If Target.Address <> Range("H3").Address' with 'If Target.Column = 8 Then' (where 8 is column H). You will also need to use the 'Target.Offset(0, 1)' method to insert the date in the adjacent columns rather than hardcoding 'I3' or 'J3'.

Will the recorded date update automatically tomorrow?

No. The VBA 'Date' command writes a static date value into the cell at the exact moment the macro is triggered. It will remain unchanged regardless of when you open the workbook next, preserving the historical integrity of your status change.

Excel & Data Problems