VBA & Macro Problems

How to Replace Number Formatting Across All Excel Worksheets Using VBA

Amos Gikunda Oct 14, 2026 869 views

Question details

The user needs a method to automatically find a specific number format and replace it with a new one across every worksheet within an Excel workbook.

How to Replace Number Formatting Across All Excel Worksheets Using VBA

Product: Excel
Device & OS: not provided
Scenario: Managing large workbooks with multiple data ranges that require bulk updates to cell formatting.
Observed behavior: The goal is to automatically locate cells utilizing an old number format string and apply a new format string to all of them globally, replacing the manual process.

Before you start

Since running a VBA macro clears the undo history and cannot be reverted using Ctrl+Z, ensure you save a backup copy of your Excel workbook before executing the code.

Solution 1Recommended

Use a VBA Macro to Replace Number Formats Globally

This solution uses a custom VBA script leveraging the Application.FindFormat and Application.ReplaceFormat properties, iterating through every sheet to apply the formatting changes.

Excel provides native VBA methods to search for specific formats and replace them. By setting the old format string and the new format string in the code, you can use a loop to instruct Excel to perform this replacement on every worksheet within the active workbook.

Open the VBA Editor

In your Excel workbook, press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the top menu, click 'Insert' and select 'Module' to create a blank coding window for your macro.

Clear Existing Format Search Criteria

In your script, include Application.FindFormat.Clear and Application.ReplaceFormat.Clear to ensure no previous search formatting interferes with your new macro.

Define Old and New Formats

Set your target format strings. Use Application.FindFormat.NumberFormat = "0.00" (your old format) and Application.ReplaceFormat.NumberFormat = "#,##0.00" (your desired new format).

Loop Through All Worksheets

Write a 'For Each' loop to iterate through ActiveWorkbook.Worksheets. Inside the loop, run ws.Cells.Replace What:="*", Replacement:="", SearchFormat:=True, ReplaceFormat:=True to apply the change across each sheet.

Run the Macro

Click anywhere inside your VBA code and press F5, or click the green 'Run' button in the toolbar, to execute the macro and update the formats.

Use a VBA Macro to Replace Number Formats Globally

Exact String Matching: The macro will only replace formats that match the FindFormat.NumberFormat string exactly. Verify your current cell number formats by pressing Ctrl+1 and checking the 'Custom' category to get the precise format string before running the code.

Advanced Macros in WPS Office

Easily Run VBA Macros for Number Formatting in WPS Office

WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to seamlessly automate repetitive tasks like replacing number formats across massive workbooks without compatibility issues.

1. Install WPS Office: Download and install WPS Office, then open your workbook using WPS Spreadsheet.
2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon and click 'VBA Editor' to launch the coding environment.
3. Paste the Macro Code: Click 'Insert' > 'Module' and paste your Application.ReplaceFormat VBA script directly into the editor.
4. Execute the Code: Press F5 to run the macro and instantly update all number formatting across your entire workbook.

Fully compatible with Microsoft Excel VBA scripts and format stringsLightweight architecture handles large, macro-enabled workbooks smoothlyFree to download with comprehensive spreadsheet automation capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Why didn't the VBA macro change all of my number formats?

The macro requires an exact match of the old number format string to identify target cells. If some cells use a slightly different custom format (e.g., an extra decimal point, a different spacing, or a specific currency symbol), the macro will skip them. Check the exact format strings of the skipped cells.

Can I undo a VBA macro after running it to revert the number formatting?

No, actions performed by VBA macros generally clear the undo stack in Excel. You cannot use Ctrl+Z to revert the formatting changes, which is why creating a backup file before running any script is highly recommended.

Does WPS Office support Excel VBA macros natively?

Yes, WPS Office Spreadsheet supports VBA macros and provides a dedicated Developer tab where you can write, edit, and execute scripts, including complex Find and Replace operations, just like in Microsoft Excel.

Excel & Data Problems