How to Highlight Cells Added or Changed During the Current Week
Question details
The user wants to automatically highlight cells that have been modified or added within the current week.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking recent updates or modifications in a worksheet to easily identify what data has changed.
- Observed behavior
- Standard conditional formatting cannot track cell modification times automatically unless the cell explicitly contains a date, requiring a VBA workaround.
Ensure your workbook is saved in a macro-enabled format (.xlsm) since this solution requires VBA code to track modification timestamps in the background.
Track Modifications Using a VBA Worksheet_Change Event
Use a hidden worksheet and a VBA macro to record the timestamp of every cell change, then apply conditional formatting based on those timestamps.
Because Spreadsheet software does not natively store the exact time a cell was last edited, standard conditional formatting is insufficient for tracking text or number changes. By using a VBA event, we can mirror the data sheet's structure in a new sheet, recording the exact date and time a cell is changed.
Insert a new worksheet in your workbook and name it exactly 'Modified'. You can later hide this sheet by right-clicking its tab and selecting 'Hide'.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer on the left, double-click the sheet where your data is entered. Paste the following code: Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("Modified").Range(Target.Address).Value = Now End Sub.
Go back to your main data sheet and select the range you want to track (e.g., A1:Z100). Click 'Home' > 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'.
Enter the formula =Modified!A1>=NOW()-7. Click 'Format', choose a fill color (like yellow) to highlight the cells, and click 'OK'.

Use Conditional Formatting for Date Entries
If the cells users are modifying actually contain dates, you can use a simple conditional formatting rule without VBA.
Use WPS Spreadsheet to Track and Highlight Cell Changes
WPS Office provides robust support for VBA macros and advanced conditional formatting, allowing you to easily track recent modifications and highlight changed cells just like in Microsoft Excel.
- 1. Download and Install WPS Office: Get WPS Office for free from the official website and open your spreadsheet file.
- 2. Enable Macros: Go to the 'Developer' tab and click 'Macros' to manage or insert your VBA tracking scripts.
- 3. Apply Conditional Formatting: Use the 'Home' tab to access conditional formatting rules and highlight your recently modified data.

Frequently Asked Questions
Can I track cell changes without using VBA?
If you only need to track when date values are entered, you can use conditional formatting with the WEEKNUM function. However, tracking modifications of standard text or numbers requires VBA to record the timestamp of the edit.
Why is my VBA macro not working when I reopen the file?
This usually happens if the file was saved as a standard .xlsx file, which strips out macros. You must save the workbook as a Macro-Enabled Workbook (.xlsm) to preserve the VBA code.
How can I hide the 'Modified' tracking sheet from other users?
Right-click the 'Modified' sheet tab at the bottom of the window and select 'Hide'. The VBA code will still be able to write timestamps to it while it remains invisible to normal users.




