VBA & Macro Problems

Highlight Changed Formula Values in Excel with VBA and Conditional Formatting

Nimra MalikNimra Malik Nov 20, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Create the baseline name

In Excel, choose Formulas > Name Manager > New. Name the range BaselineValues and set Refers to to ='Copy of values'!$E$15:$I$100.

2
Add the opening macro

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

3
Save and capture the baseline

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.

4
Create the highlighting rule

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.

5
Test the comparison

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.

Opening refresh timing: The macro calculates formulas when the workbook opens, but it does not wait for asynchronous external data refreshes. If those affect your results, refresh the data first and capture the baseline manually after refresh completes.
Compare formula results

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. 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. 2. Name the baseline: Open Formulas > Name Manager or Define Name, and create BaselineValues referring to ='Copy of values'!$E$15:$I$100.
  3. 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. 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.
Highlights formula results that differ from a saved baselineOpens and saves Microsoft Excel .xlsx filesUses familiar spreadsheet formulas and formatting toolsProvides a manual approach that does not require VBA
microsoft office alternative - wps office

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.