VBA & Macro Problems

How to Apply the Same Excel Color Scale to Another Column

Emma Brown Oct 10, 2026 868 views

Question details

The user needs to apply a value-based conditional formatting color scale to a target column so that it accurately reflects or mirrors the color scale of a source column.

How to Apply the Same Color Scale to Another Column in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Visualizing data where formatting in one column needs to match the color scaling rules or calculated background colors of another column.
Observed behavior: Standard conditional formatting does not natively link the visual formatting of one cell directly to another without applying equivalent rules or utilizing VBA code.

Before you start

Before proceeding, determine the minimum, midpoint, and maximum value thresholds used in your source column's color scale. If you plan to use the VBA method, ensure you save your file as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use Matching Conditional Formatting Rules

This is the most reliable and performance-friendly approach. By applying identical conditional formatting rules with hardcoded value thresholds to your target column, it will visually mirror the scale.

Select the Target Column

Highlight all the cells in the target column (for example, Column G) where you want the new color scale to appear.

Open the Conditional Formatting Menu

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Configure the Color Scale

Choose 'Format all cells based on their values'. Select either a 2-Color Scale or 3-Color Scale from the Format Style dropdown. Input the exact numeric thresholds (minimum, midpoint, maximum) that were used to format your source column (Column M).

Apply and Verify

Click 'OK' to apply the rule. Verify that the colors in the target column now accurately reflect the data ranges based on the rules you defined.

Use Matching Conditional Formatting Rules

Solution 2

Use a Worksheet_Change VBA Macro

If the target column must exactly mirror the dynamically calculated color of the source column regardless of its own values, use a VBA macro to copy the displayed color.

Advanced Spreadsheet Formatting

Easily Apply Conditional Formatting and Macros in WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive interface for setting up complex conditional formatting rules and fully supports VBA macros, allowing you to seamlessly link color scales across columns without hassle.

1. Open Your Workbook in WPS: Launch WPS Office and open your existing spreadsheet document.
2. Select the Target Range: Highlight the column where you want the color scale applied or mirrored.
3. Apply Formatting or Macros: Navigate to the Home tab to use the built-in Conditional Formatting tool, or go to the Developer tab to open the VBA editor and insert your automated color-syncing code.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats and VBA macros.Intuitive Conditional Formatting manager for duplicating color scales across multiple ranges.Lightweight software that processes complex macros efficiently without heavy performance overhead.Free to use with a familiar, easy-to-navigate tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the Format Painter to copy color scales to another column?

The Format Painter copies the conditional formatting rules, not just the static color. If your target column contains different data values, the resulting colors will be calculated based on the target column's data, which may not visually match the source column unless the threshold rules are adjusted.

Why is my VBA macro not updating the cell colors automatically?

Ensure that your macro is placed in the specific Worksheet module (e.g., Sheet1) rather than a standard Module, and that it utilizes the Worksheet_Change event. Additionally, confirm that macros are enabled in your Trust Center settings.

How do I extract the exact color from a conditionally formatted cell using VBA?

In VBA, you cannot use the standard .Interior.Color property to retrieve a conditionally formatted color. You must use the .DisplayFormat.Interior.Color property to capture the actual color currently displayed on the screen.

Does mirroring color scales with VBA slow down my spreadsheet?

Using built-in Conditional Formatting rules is generally efficient. However, using a Worksheet_Change VBA macro to constantly update cell colors can cause processing overhead or lag, especially if your workbook contains thousands of rows or complex calculations.

Excel & Data Problems