Highlight Changed Formula Values in Excel with VBA and Conditional Formatting
Question details
Highlight formula cells whose displayed values differ from the values recorded when the workbook opened.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Formulas in Main Sheet!E15:I100 depend on data in the Components worksheet. The user wants to see which results have changed during the session.
- Observed behavior
- Formula results can recalculate after another worksheet changes, but standard worksheet change events may not detect those recalculated results.
Create worksheets named Main Sheet and Copy of values, or adjust the names and ranges below to match your workbook. Make sure the formula results have calculated and any required external data has refreshed before capturing the baseline.
Capture an opening baseline and compare it with conditional formatting
Use a workbook-opening macro to record the initial values, then highlight results that differ from that snapshot.
The comparison shows the current difference from the opening baseline, not a permanent history of every change. If a value returns to its original value, its highlighting disappears.
The rule below is intended for numeric results. Formula errors may not be highlighted by this comparison.
In Excel, choose Formulas > Name Manager > New. Name the range BaselineValues and set Refers to to ='Copy of values'!$E$15:$I$100.
Press Alt+F11, find your workbook in Project Explorer, and double-click ThisWorkbook. Add this code: Private Sub Workbook_Open() Application.Calculate Worksheets("Copy of values").Range("E15:I100").Value = _ Worksheets("Main Sheet").Range("E15:I100").Value End Sub
Choose File > Save As and save as an Excel Macro-Enabled Workbook (.xlsm). Close and reopen it, then enable macros if prompted so Workbook_Open captures the calculated values.
On Main Sheet, select E15:I100. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =E15<>INDEX(BaselineValues,ROW()-ROW($E$15)+1,COLUMN()-COLUMN($E$15)+1), choose Format > Fill, select a color, and confirm.
Change a source value on Components and check Main Sheet!E15:I100 for highlighted results. If desired, right-click the Copy of values sheet tab and select Hide.
Create a manual baseline without VBA
Use this option when macros are unavailable or when you want to choose exactly when the baseline is captured.
Highlight changed formula values in WPS Spreadsheets
Use a values-only snapshot and a conditional formatting formula in WPS Spreadsheets to compare current formula results with a baseline. Refresh the snapshot manually whenever you want to start a new comparison.
- 1. Create a snapshot: Open the workbook in WPS Spreadsheets. Copy Main Sheet!E15:I100, select Copy of values!E15, and use Paste Special > Values.
- 2. Name the baseline: Open Formulas > Name Manager or Define Name, and create BaselineValues referring to ='Copy of values'!$E$15:$I$100.
- 3. Add a comparison rule: Select Main Sheet!E15:I100, then open Home > Conditional Formatting and create a formula-based rule. Enter =E15<>INDEX(BaselineValues,ROW()-ROW($E$15)+1,COLUMN()-COLUMN($E$15)+1).
- 4. Choose a fill and test: Set a fill color for the rule and confirm. Change a source value on Components and check whether the affected formula result is highlighted.

Frequently Asked Questions
Why doesn't Worksheet_Change detect every formula result change?
Worksheet_Change responds to cell edits, but a formula result can change through recalculation without an edit to the formula cell. Comparing the result with a saved baseline avoids relying on that event.
What happens if a value changes back to its opening value?
The conditional formatting rule removes the highlight because the current value again matches the baseline. A separate change log would be needed to preserve a history of changes.
How do I reset the comparison?
For the opening-macro method, close and reopen the workbook with macros enabled. For the manual method, copy Main Sheet!E15:I100 and use Paste Special > Values at Copy of values!E15.
Can I hide the baseline worksheet?
Yes. Right-click the Copy of values sheet tab and select Hide. The named range and conditional formatting can still use the values on the hidden sheet.
Why might the opening baseline contain stale values?
External data may still be refreshing when Workbook_Open runs. Wait for the refresh to finish, then copy the formula range and use Paste Special > Values on the baseline sheet.




