VBA & Macro Problems

How to Create VBA Code to Toggle Specific Cell Fill Colors in Excel

Adam Davis Oct 9, 2026 869 views

Question details

The user needs a VBA macro that toggles the fill color of specifically highlighted cells, removing and restoring the color only for those original cells without applying it to every other cell in the worksheet.

How to Create a VBA Macro to Toggle Specific Cell Fill Colors

Product: Excel
Device & OS: not provided
Scenario: Running a VBA script to hide and show specific cell highlights across a worksheet.
Observed behavior: The current basic If/Else loop incorrectly colors all previously uncolored cells yellow when attempting to toggle, rather than exclusively restoring the originally yellow cells.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet program and remember to save your file as a Macro-Enabled Workbook (*.xlsm) to preserve your VBA code.

Solution 1Recommended

Use a Module-Level Variable to Store the Target Range

This is the recommended approach. By declaring a module-level variable, the macro can remember exactly which cells were colored before clearing them, allowing for a clean toggle.

A common mistake is using an 'Else' branch that simply colors any non-yellow cell yellow. Instead, you must store the specific range of yellow cells into memory before removing their fill. When the macro runs again, it reapplies the color exclusively to that stored range.

Open the VBA Editor

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'. This ensures the variable remains accessible between macro executions.

Declare the Module-Level Variable

At the very top of the code window (above any 'Sub' routines), type 'Dim savedRange As Range'. This variable will hold the cell addresses temporarily.

Write the Toggle Logic

Create a macro that checks if 'savedRange' is empty. If it is, loop through the sheet, add yellow cells to 'savedRange', and clear their color. If 'savedRange' is not empty, restore the yellow color to 'savedRange' and clear the variable.

Use a Module-Level Variable to Store the Target Range

Memory Limitation: Module-level variables are cleared when the workbook is closed. The toggle memory will only persist while the file remains open.

Solution 2

Persist the Cell Range in a Hidden Worksheet

Use this method if you need the macro to remember which cells to toggle even after the workbook has been closed and reopened.

Advanced Macros in WPS Office

Write and Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office fully supports Excel VBA macros, allowing you to automate tasks and toggle cell colors with the exact same code you use in Microsoft Excel.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the top ribbon, and ensure the Developer tab is active.
2. Access the VBA Editor: Click on 'VBA Editor' in the Developer tab to open the familiar coding environment.
3. Paste Your Code: Insert a new Module and paste your color toggling VBA script exactly as you would in Excel.
4. Run the Macro: Assign your macro to a button or run it directly to seamlessly toggle your cell highlights.

Fully compatible with Microsoft Excel (.xlsm) macro filesSupports standard VBA syntax and objects like Range and Interior.ColorLightweight, fast, and free to use for daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA toggle macro color every other cell yellow?

Your code likely uses a simple If/Else loop that checks if a cell is yellow. If a cell has no fill, the 'Else' statement applies the yellow color. This incorrectly affects all previously blank cells in the used range instead of just the originally highlighted ones.

Will my stored VBA range variable reset if I close the workbook?

Yes, module-level variables are cleared from system memory when the workbook is closed. To retain the toggle memory across sessions, you must save the cell addresses to a physical cell in a hidden sheet.

How do I save my workbook so the VBA macro isn't lost?

You must use 'Save As' and select the 'Excel Macro-Enabled Workbook' (*.xlsm) format. Standard .xlsx files are specifically designed to strip out VBA code for security reasons.

Excel & Data Problems