VBA & Macro Problems

How to Automatically Apply Excel Cell Colors Based on Specific Values Using VBA

Anum Mushtaq Oct 22, 2026 868 views

Question details

The user needs to dynamically format cell background colors across an entire workbook based on specific text inputs, copying the color from predefined reference cells.

How to Automatically Apply Excel Cell Colors Based on Specific Values Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating a workbook where entering 'O' copies the fill color from Column A, entering 'U' copies the fill color from Row 1, and deleting the value clears the cell color.
Observed behavior: The user wants a scalable solution using a VBA Worksheet_Change or SheetChange event to monitor a specific grid range (like B2:ZZ500) and automatically apply or clear colors based on cell values.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA scripts to run, and verify that the Developer tab is enabled in your ribbon settings.

Solution 1Recommended

Use a Workbook-Level SheetChange Event

Implement a macro at the workbook level so the dynamic coloring rules apply to all identical grid templates across different worksheets without duplicating code.

By utilizing the Workbook_SheetChange event, Excel will monitor every sheet for user input. If the modified cell falls within your target range (e.g., B2:ZZ500), the VBA code checks the value entered and applies the corresponding interior color from the reference cells.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.

Access the Workbook Module

In the Project Explorer pane on the left, double-click on 'ThisWorkbook' to open the workbook-level code window.

Initialize the SheetChange Event

From the top dropdowns, select 'Workbook' and 'SheetChange'. This creates the private sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range).

Define the Target Range

Add an Intersect function to ensure the macro only runs when cells within B2:ZZ500 are modified. For example: If Not Intersect(Target, Sh.Range("B2:ZZ500")) Is Nothing Then.

Apply Color for Value 'O'

Add a conditional statement that checks if Target.Value = "O". If true, set the cell color to match Column A: Target.Interior.Color = Sh.Cells(Target.Row, 1).Interior.Color.

Apply Color for Value 'U'

Add an ElseIf statement for the value 'U'. Set the cell color to match Row 1: Target.Interior.Color = Sh.Cells(1, Target.Column).Interior.Color.

Clear the Fill Color

Add an Else statement to handle deleted entries or other text values. Use Target.Interior.ColorIndex = xlColorIndexNone to remove any background fill.

Macro Security: Remember to enable macros in your Trust Center settings, otherwise the Workbook_SheetChange event will not trigger when you enter values.

Automate Spreadsheets with WPS Office

Use Macros and VBA Effectively in WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros, enabling you to automate dynamic cell coloring and repetitive formatting tasks seamlessly.

1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing your template.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon to reveal the macro and VBA options.
3. Launch the VBA Editor: Click the 'VBA Editor' button to paste or modify your Workbook_SheetChange code directly within the WPS interface.
4. Save and Execute: Save your document securely as a macro-enabled format to preserve your automated cell coloring rules.

Fully compatible with Microsoft Excel .xlsm files and VBA scriptsLightweight application that loads large macro-enabled files quicklyBuilt-in Developer tools for advanced scripting without extra add-insFree and intuitive interface for managing complex spreadsheet templates

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA code changing the cell colors automatically?

Your macro settings may be disabled. Go to the Developer tab, click on Macro Security, and select 'Enable all macros'. Additionally, ensure you placed the code in the 'ThisWorkbook' module rather than a standard module.

Can I apply this dynamic coloring macro to only one specific worksheet?

Yes. Instead of using the Workbook_SheetChange event in 'ThisWorkbook', right-click the specific sheet tab, select 'View Code', and place your VBA logic inside a private Worksheet_Change event for that sheet.

How do I ensure the color is removed when a cell value is deleted?

Include an 'Else' statement at the end of your IF conditions in the VBA script that sets 'Target.Interior.ColorIndex = xlColorIndexNone'. This explicitly tells Excel to remove the fill color if the cell is cleared or contains an unassigned value.

Excel & Data Problems