VBA & Macro Problems

Highlight Cells Changed by Power Query Refresh in Excel VBA

Kushani Nimanthika Oct 10, 2026 868 views

Question details

The user needs a way to highlight Excel cells automatically when their values are updated following a Power Query refresh.

How to Highlight Cells Changed by Power Query Refresh in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Tracking and visually identifying data changes that are automatically triggered by a background Power Query refresh.
Observed behavior: The standard Worksheet_Change VBA event fails to trigger because query results and formulas update automatically rather than through manual cell edits.

Before you start

Ensure your workbook contains an active Power Query connection and save your file as an Excel Macro-Enabled Workbook (.xlsm) so the VBA code can execute properly.

Solution 1Recommended

Use Worksheet_Calculate to Track and Highlight Cell Changes

Since the Worksheet_Change event does not detect query updates, use a Scripting.Dictionary inside the Worksheet_Calculate event to compare previous and current values.

This solution stores the initial values of your formulas during the first calculation. When a Power Query refresh triggers a recalculation, the macro compares the new values against the stored ones and highlights any differences.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor.

Access the Specific Worksheet Module

In the Project Explorer pane on the left, double-click the specific worksheet (e.g., Sheet1) where your Power Query table or formulas reside.

Insert the Tracking Macro

Paste the following code into the module window: Private previousValues As Object Private Sub Worksheet_Calculate() Dim formulaRange As Range Dim cell As Range If previousValues Is Nothing Then Set previousValues = CreateObject("Scripting.Dictionary") End If On Error Resume Next Set formulaRange = Me.UsedRange.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If formulaRange Is Nothing Then Exit Sub For Each cell In formulaRange If previousValues.Exists(cell.Address) Then If cell.Value <> previousValues(cell.Address) Then cell.Interior.Color = RGB(255, 153, 153) End If End If previousValues(cell.Address) = cell.Value Next cell End Sub

Save and Trigger Refresh

Close the VBA editor and save the file as an .xlsm document. Refresh your Power Query; the first calculation initializes the script, and subsequent refreshes will highlight changed cells in red.

Use Worksheet_Calculate to Track and Highlight Cell Changes

Initialization Requirement: The macro will not highlight anything on the very first calculation because it needs to build the initial dictionary of values. It will highlight changes on the second calculation and onward.

Solution 2

Understand the Limitations of Worksheet_Change Event

Explains why the standard Worksheet_Change event is ineffective for Power Query automation.

Free Microsoft Office alternative

Looking for a Lightweight Alternative to Microsoft Excel?

If complex Excel VBA limitations and Power Query workarounds are slowing you down, consider WPS Office. It is a highly compatible, free, and lightweight alternative that easily handles your daily spreadsheet and macro needs with a familiar interface.

1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm files directly.
3. Enable Macros: Navigate to the Developer tab to manage and run your VBA macros just as you would in Excel.

Free to use with a familiar, easy-to-navigate interface.Highly compatible with Microsoft Excel macro formats (.xlsx, .xlsm, .csv).Lightweight design ensures fast loading and smooth performance without bloatware.Includes built-in Developer tools for running custom VBA scripts seamlessly.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the Worksheet_Change event work for formula updates?

The Worksheet_Change event in VBA is designed exclusively to capture direct, manual edits made by a user in a cell. It ignores changes driven by formula recalculations or background data connections like Power Query.

How do I access the specific worksheet module to paste the code?

Right-click the sheet tab at the bottom of your Excel window (e.g., 'Sheet1') and select 'View Code' from the context menu. This directly opens the correct module where the Worksheet_Calculate code should be pasted.

Will this macro slow down my Excel workbook?

Because the Worksheet_Calculate event triggers every time the worksheet recalculates, it may cause a slight performance delay in very large workbooks with thousands of formulas. Using the Scripting.Dictionary object minimizes this delay, but it's best applied to targeted sheets.

How do I clear the highlighted cells before the next refresh?

You can manually select the cells, go to the Home tab, and choose 'No Fill' from the paint bucket tool. Alternatively, you can add a separate macro that clears the interior color of the used range before you trigger a new Power Query refresh.

Excel & Data Problems