VBA & Macro Problems

How to Highlight Cells Added or Changed During the Current Week

Huma Ashraf ChHuma Ashraf Ch Oct 29, 2026 872 views

Question details

The user wants to automatically highlight cells that have been modified or added within the current week.

How to Highlight Cells Added or Changed During 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.
Before you start

Ensure your workbook is saved in a macro-enabled format (.xlsm) since this solution requires VBA code to track modification timestamps in the background.

Solution 1Recommended

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.

1
Create a tracking worksheet

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'.

2
Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

3
Add the Worksheet_Change code

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.

4
Apply conditional formatting

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'.

5
Enter the formula and format

Enter the formula =Modified!A1>=NOW()-7. Click 'Format', choose a fill color (like yellow) to highlight the cells, and click 'OK'.

Track Modifications Using a VBA Worksheet_Change Event
Save as Macro-Enabled: Remember to save your file as a Macro-Enabled Workbook (*.xlsm) so the VBA code continues to function correctly upon reopening.
Advanced Data Tracking with WPS Spreadsheet

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. 1. Download and Install WPS Office: Get WPS Office for free from the official website and open your spreadsheet file.
  2. 2. Enable Macros: Go to the 'Developer' tab and click 'Macros' to manage or insert your VBA tracking scripts.
  3. 3. Apply Conditional Formatting: Use the 'Home' tab to access conditional formatting rules and highlight your recently modified data.
Fully compatible with Microsoft Excel .xlsm and .xlsx formatsBuilt-in VBA editor for running and editing macrosAdvanced conditional formatting for dynamic data visualizationFree and lightweight alternative to heavy office suites
microsoft office alternative - wps office

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.