VBA & Macro Problems

How to Use a VBA Macro to Change Cell Formatting Across All Worksheets

Rana Garcia Oct 12, 2026 868 views

Question details

The user needs to change the formatting (e.g., font color) of a specific cell across every worksheet in a workbook simultaneously using a VBA macro.

How to Change Cell Formatting Across All Worksheets Using a VBA Macro

Product: Spreadsheets (Excel / WPS Spreadsheet)
Device & OS: not provided
Scenario: Automating repetitive formatting tasks across multiple sheets to save time, avoid manual clicks, and ensure workbook consistency.
Observed behavior: To successfully format a specific cell in all sheets, the macro must use a worksheet loop with a fully qualified range reference instead of relying on the active sheet.

Before you start

Before running the macro, ensure you have enabled Developer Tools and macros in your spreadsheet application. It is highly recommended to save a backup copy of your workbook, as actions performed by a VBA macro cannot be undone using the standard Undo feature.

Solution 1Recommended

Use a For Each Loop with Qualified Worksheet References

This is the most reliable method. It iterates through every worksheet in the workbook and applies the formatting directly to the specified range on each sheet.

When looping through worksheets, relying on the 'ActiveSheet' can lead to errors or result in formatting being applied only to the currently viewed sheet. By explicitly qualifying the range with the worksheet variable (e.g., wrkSht.Range), you guarantee the correct cell is formatted exactly on every sheet in the loop.

Open the VBA Editor

Open your workbook and press the ALT + F11 keys simultaneously to launch the Visual Basic for Applications (VBA) Editor.

Insert a New Module

Click 'Insert' from the top menu bar and select 'Module' to create a blank canvas for your macro code.

Paste the VBA Loop Code

Copy and paste the following script into the module window: Sub CostHiding() Dim wrkSht As Worksheet For Each wrkSht In ThisWorkbook.Worksheets wrkSht.Range("F17").Font.ColorIndex = 2 Next wrkSht End Sub

Customize the Range and Formatting

Modify 'F17' to match the target cell you want to format. Change 'ColorIndex = 2' to your desired color code (e.g., 3 for red), or replace it with 'Font.Color = RGB(255, 0, 0)' for specific RGB values.

Run the Macro

Press F5 on your keyboard or click the green 'Run' play button in the toolbar to execute the macro and format all sheets instantly.

Use a For Each Loop with Qualified Worksheet References

Understanding ThisWorkbook: Using 'ThisWorkbook.Worksheets' instead of just 'Sheets' ensures the macro only modifies the workbook that actually contains the VBA code. This prevents accidental formatting changes if you have other spreadsheet files open.

Advanced Spreadsheet Automation

Automate Tasks Effortlessly with WPS Spreadsheet

WPS Office provides robust built-in support for VBA and macros, allowing you to easily automate repetitive formatting tasks across all your worksheets. It offers a familiar interface, ensuring your existing Excel macros run smoothly without modification.

1. Open Developer Tools: Launch WPS Spreadsheet, navigate to the 'Developer' tab on the top ribbon, and click 'VBA Editor'.
2. Insert a Module: In the VBA Editor, right-click your workbook name in the left Project Explorer pane, select 'Insert', and choose 'Module'.
3. Run Your Macro: Paste your formatting loop code into the module, customize the cell reference to your needs, and click 'Run' to apply the formatting across all sheets.

Full support for VBA (Visual Basic for Applications) macro scriptingSeamlessly compatible with Microsoft Excel formats (.xlsx, .xls, and .xlsm)Lightweight, fast, and completely free to useFamiliar Developer Tools interface for easy code editing

microsoft office alternative - wps office

Frequently Asked Questions

Why is my macro only formatting the active sheet instead of all sheets?

This happens if you use an unqualified range reference (like `Range("F17")`) inside your loop. You must attach it to the loop variable, such as `wrkSht.Range("F17")`. Without the prefix, Excel or WPS assumes you want to format the currently active sheet over and over again.

How do I change the font color to a specific RGB value instead of using ColorIndex?

Replace the `.Font.ColorIndex = 2` portion of your VBA code with `.Font.Color = RGB(255, 0, 0)` (where 255, 0, 0 represents pure red). Using RGB gives you access to exact color matching rather than the limited palette provided by ColorIndex.

Can I format a range of multiple cells across all worksheets instead of just one?

Yes. You simply need to expand the range reference in the macro. For example, changing the code to `wrkSht.Range("A1:D10").Font.Bold = True` will apply bold text formatting to the entire A1 through D10 block on every worksheet.

Excel & Data Problems