How to Automatically Set Status and Close Date Using Excel VBA
Question details
The user wants to automate a status tracker so that entering a close date changes the status to 'Closed', and selecting 'Closed' auto-fills today's date.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing an issue tracker or task list in Excel where status and completion dates need to remain synchronized without manual double-entry.
- Observed behavior
- The goal is to automatically update corresponding cells in the same row when either the status column or the close date column is modified by utilizing VBA.
Ensure your Excel workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Trust Center settings to allow the VBA code to run properly.
Use Worksheet_Change Event to Automate Status and Date Updates
Implement a VBA macro on the specific worksheet to monitor target columns and update related cells automatically.
This method uses the Worksheet_Change event to listen for edits in your status and date columns. To avoid infinite loops where the macro triggers itself, the code uses Application.EnableEvents to temporarily pause event tracking while updating the target cells.
Right-click the sheet tab at the bottom of Excel where your tracker is located and select 'View Code', or press ALT + F11 to open the Visual Basic for Applications window.
In the code window for your specific sheet, select 'Worksheet' from the left dropdown and 'Change' from the right. This will automatically generate the Private Sub Worksheet_Change(ByVal Target As Range) block.
Inside the sub, write an 'If Target.Column = [YourDateColumnNumber]' statement. If a date is entered, use Target.Offset to update the status column to 'Closed'. Add an 'ElseIf Target.Column = [YourStatusColumnNumber]' to check if the status is set to 'Closed' and use Target.Offset to input 'Date' (today's date) in the date column.
Wrap your cell-value-changing code inside 'Application.EnableEvents = False' and 'Application.EnableEvents = True'. This is crucial to prevent the Worksheet_Change event from recursively calling itself and crashing Excel.

Use WPS Spreadsheet to Run and Edit VBA Macros
WPS Office provides excellent compatibility with Excel macros, allowing you to use Worksheet_Change events to automate your task trackers smoothly.
- 1. Open your XLSM file in WPS: Launch WPS Spreadsheet and open your macro-enabled tracking file.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab in the top ribbon and click on 'Macros' or 'Visual Basic' to access the VBA editor.
- 3. Enable Macros: If prompted by the security warning below the ribbon, click 'Enable Macros' to allow the Worksheet_Change event to trigger.
- 4. Test the Automation: Type a date into your target column or enter 'Closed' in the status column to see the corresponding cells update automatically.

Frequently Asked Questions
Why does my Excel freeze or crash when the macro runs?
This usually happens because changing a cell within a Worksheet_Change event triggers the event again, causing an infinite loop. Always use Application.EnableEvents = False before changing cell values, and set it back to True immediately after.
Can I use this VBA code on multiple sheets at once?
Yes, instead of placing the code in a specific worksheet module, you can place it in the ThisWorkbook module under the Workbook_SheetChange event to apply it across the entire workbook.
How do I handle users clearing the date or status cells?
You should add an If IsEmpty(Target) or Target.Value = "" condition in your VBA code to either exit the sub or clear the corresponding adjacent cell to maintain accurate tracking.
Why is my macro not running when I type a date?
Make sure that your workbook is saved as an .xlsm file, macros are enabled in the Excel Trust Center, and the code is placed in the correct worksheet module rather than a standard module.




