VBA & Macro Problems

How to Update an Excel VBA Result Cell When Source Cells Change

Khadija Khan Oct 8, 2026 874 views

Question details

The user wants a VBA-generated result cell to display as a formula and automatically recalculate whenever specific referenced source cells are modified.

How to Update an Excel VBA Result Cell When Source Cells Change

Product: Excel
Device & OS: not provided
Scenario: Writing a VBA macro to maintain a dynamic formula in a specific cell and automatically trigger a recalculation when any of the monitored source data ranges are updated.
Observed behavior: The user needs to implement the Worksheet_Change event in VBA to monitor multiple non-contiguous ranges and execute a calculation on the result cell when those ranges change.

Before you start

Ensure your workbook is saved in a macro-enabled format (.xlsm) and that you have enabled macros in your application's Trust Center settings.

Solution 1Recommended

Use Worksheet_Change Event and Intersect Method

This solution uses the Worksheet_Change event to actively monitor specific source ranges and uses VBA's Formula property to ensure the result cell recalculates dynamically.

By placing your VBA code within the specific Worksheet module instead of a standard module, you can leverage the Worksheet_Change event. The Intersect method ensures the recalculation only triggers when edits happen inside your designated source ranges, keeping your workbook optimized.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet application.

Access the Target Worksheet Module

In the Project Explorer pane on the left, double-click the specific worksheet (e.g., Sheet1) where your source cells and result cell reside. Do not put this code in a standard module.

Set the Initial Formula

In your macro, assign the complete formula directly to the result cell using the Formula property. For example: Range("R2").Formula = "=SUM(H2:H6, L2:L6)". Avoid using ellipses; type the full formula out.

Write the Worksheet_Change Code

Create the event subroutine by typing: Private Sub Worksheet_Change(ByVal Target As Range). This runs automatically every time a cell is edited on this sheet.

Apply the Intersect Method to Monitor Source Cells

Inside the subroutine, check if the edited cell is within your source ranges using: If Not Intersect(Target, Range("H2:H6, L2:L6, G2:G6, M2:M6, N2:N11")) Is Nothing Then.

Recalculate the Result Cell

Within the If statement block, execute the recalculation for your specific result cell by adding the code: Range("R2").Calculate. Close the statement with End If.

Use Worksheet_Change Event and Intersect Method

Optimization Tip: Using Application.EnableEvents = False before the recalculation and Application.EnableEvents = True immediately after can prevent your code from accidentally looping if your macro alters other cells.

Advanced Macro Support in WPS Office

Write and Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides excellent built-in support for VBA and macros, allowing you to use events like Worksheet_Change and automate complex data operations just as you would in Microsoft Excel.

1. Launch WPS Spreadsheet: Download and install WPS Office, then open your macro-enabled workbook in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the 'Developer' tab located on the top ribbon of the WPS Spreadsheet interface.
3. Open the VBA Editor: Click on the 'Visual Basic' button or use the Alt + F11 shortcut to open the integrated VBA environment.
4. Insert Your Code: Double-click the desired worksheet in the Project window and paste your Worksheet_Change event scripts exactly as authored.

Highly compatible with Microsoft Excel macro-enabled formats (.xlsm)Built-in VBA editor equipped with code formatting and debugging toolsSeamless execution of standard Excel VBA syntax and event handlersLightweight application footprint with fast loading and calculation speeds

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my Worksheet_Change event triggering when cells are updated?

This commonly happens if macros are disabled in your security settings, or if 'Application.EnableEvents = False' was triggered by a previous macro and never set back to True. Open the Immediate Window in the VBA editor (Ctrl + G) and run 'Application.EnableEvents = True' to fix it.

Can I use the Worksheet_Change event to monitor multiple separate worksheets?

The Worksheet_Change event is specific to a single sheet. To monitor multiple sheets simultaneously, you should place your code in the 'ThisWorkbook' module and use the 'Workbook_SheetChange' event instead.

What is the difference between Range.Formula and Range.Value in VBA?

'Range.Formula' assigns an actual spreadsheet formula to the cell (such as '=A1+B1'), which will dynamically update based on workbook calculation settings. 'Range.Value' only inserts the static calculated result at the time the macro is run.

Excel & Data Problems