VBA & Macro Problems

How to Keep Excel Undo Available After Running VBA Macros

Nimra Malik Sep 25, 2026 870 views

Question details

The user wants to preserve the Excel Undo history when running a VBA macro, specifically for dynamic features like row highlighting.

How to Keep Excel Undo Available After Running VBA Code

Product: Excel
Device & OS: not provided
Scenario: Setting up an automated row-highlighting feature using VBA without losing the ability to undo previous actions in the workbook.
Observed behavior: Running VBA code that modifies cell values or formatting automatically clears the Excel Undo stack, preventing the user from reverting prior changes.

Before you start

Ensure you are familiar with the Visual Basic Editor and have applied Conditional Formatting to your target ranges, as this method relies on triggering recalculations rather than directly changing cell formats.

Solution 1Recommended

Use Worksheet Calculation to Trigger Conditional Formatting

Instead of using VBA to modify cell background colors, use VBA to recalculate the sheet and let Conditional Formatting handle the visual changes to preserve the Undo stack.

By default, any VBA command that writes data or alters formatting on a worksheet will instantly clear Excel's Undo history. However, commands that do not alter the workbook structure or cell properties directly, such as Application.Calculate, do not wipe the Undo memory.

To achieve dynamic row highlighting without losing Undo functionality, you can pair the CELL("row") function inside a Conditional Formatting rule with a lightweight VBA macro that merely forces the worksheet to recalculate whenever the selection changes.

Apply Conditional Formatting

Select the data range where you want row highlighting. Go to Home > Conditional Formatting > New Rule. Choose 'Use a formula to determine which cells to format', and enter the formula: =ROW()=CELL("row"). Choose your desired background color and click OK.

Open the Visual Basic Editor

Press Alt + F11 to open the VBA Editor. In the Project Explorer pane on the left, double-click the specific worksheet name (e.g., Sheet1) where you applied the formatting.

Insert the Recalculation Code

Paste the following code into the worksheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Application.CutCopyMode = False Then On Error Resume Next Application.Calculate End If End Sub

Test the Solution

Close the VBA Editor and return to your worksheet. Click on different rows to see the highlight update dynamically. Make a test edit in a cell, change your selection, and press Ctrl + Z to confirm the Undo function is still available.

Use Worksheet Calculation to Trigger Conditional Formatting

Why check CutCopyMode?: The line 'If Application.CutCopyMode = False' ensures that the recalculation does not interrupt or clear your clipboard when you are trying to copy and paste data across cells.

Advanced Spreadsheet Features

Run Macros and Preserve Data Safely with WPS Spreadsheet

WPS Office provides robust and highly compatible support for VBA and macros. You can seamlessly run scripts like Application.Calculate without disrupting your daily workflow, all within a lightweight and intuitive interface.

1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and install it on your device.
2. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the dynamic highlighting.
3. Enable Macros: Click 'Enable Macros' on the security warning prompt that appears below the ribbon to allow your code to run.
4. Access the Developer Tools: Navigate to the Developer tab and click the 'Visual Basic' icon to manage your Worksheet_SelectionChange events easily.

Advanced VBA editor integrated directly into WPS SpreadsheetHigh compatibility with Microsoft Excel .xlsm and .xlsb formatsLightweight application that processes complex calculations quicklyFree to use with a familiar, easy-to-navigate user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I keep the Undo history if my VBA macro actually writes values to cells?

No. Natively, Excel and most spreadsheet applications clear the Undo stack whenever a VBA script modifies a cell's value, formula, or format. You can only preserve Undo by restricting VBA to non-modifying actions like recalculation or querying data.

Why does my old row highlighting macro break the Undo function?

Standard row highlighting macros typically work by changing the cell's 'Interior.ColorIndex' property directly through VBA. Because it alters the formatting properties of the worksheet, the application clears the Undo memory to prevent conflicts.

How can I apply this method to highlight columns instead of rows?

You can use the exact same VBA recalculation code. However, you will need to change your Conditional Formatting formula to use the column equivalent: =COLUMN()=CELL("col").

Excel & Data Problems