VBA & Macro Problems

How to Automatically Copy Excel Values to Cells by Fill Color

Camila Milosovich Sep 27, 2026 869 views

Question details

The user wants to automatically copy a specific value to all other cells in an Excel worksheet that share the same background fill color.

How to Automatically Copy Excel Values to Cells by Fill Color in Excel

Product: Excel
Device & OS: not provided
Scenario: Populating multiple cells with the same data based exclusively on their background color formatting across the worksheet.
Observed behavior: Excel lacks a built-in worksheet function to copy or fill values based on cell color, requiring a VBA macro to automate the task. Users may also encounter execution errors if company IT policies block macros.

Before you start

Ensure that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and verify with your IT department that company security policies allow the execution of VBA macros.

Solution 1Recommended

Use a VBA Macro to Copy Values by Cell Color

Create and run a custom VBA macro to automatically find cells with a specific background color and populate them with a selected value.

Excel does not have a native formula to evaluate cell colors, making VBA the most effective method for this operation.

This macro will prompt you to select a source cell, read its background color and value, and then automatically apply that value to every matching colored cell within the worksheet's used range.

Open the VBA Editor

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

Insert a New Module

Click on 'Insert' in the top menu bar and select 'Module' to create a new blank script window.

Paste the Macro Code

Paste your custom VBA macro code into the newly created module window, then close the VBA editor.

Run the Macro

Back in your Excel worksheet, press Alt + F8 to open the Macro dialog box. Select your newly created macro from the list and click 'Run'.

Select the Source Cell

Follow the on-screen prompt generated by the macro to select the colored cell containing the value you want to copy.

Use a VBA Macro to Copy Values by Cell Color

Macro Security Blocked: If you receive an error when attempting to run the macro, your company's IT security policies may be blocking macro execution. You may need to adjust your Trust Center settings or contact your IT administrator.

Advanced Automation with WPS Office

Easily Manage Cell Data and Macros with WPS Spreadsheet

WPS Spreadsheet offers robust support for VBA macros, allowing you to easily automate tasks like copying values by fill color. It is a highly compatible and lightweight alternative for your daily data processing needs.

1. Open your Workbook in WPS: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm document.
2. Access the Macro Tool: Go to the 'Tools' tab and select 'Macros', or simply press Alt+F8 to open the macro dialog window.
3. Create a New Macro: Type a name for your macro in the input field and click 'Create' to launch the built-in VBA Editor.
4. Paste and Save Code: Paste your color-matching VBA script into the module space and save your document.
5. Execute the Task: Run the macro from the 'Tools' tab to automatically populate matching colored cells with your desired value.

Fully compatible with Microsoft Excel formats, including .xlsm macro workbooksNative support for VBA and macros to automate workflows like color-based copyingFamiliar user interface that requires no learning curve to execute macrosLightweight application that runs smoothly and efficiently on most devices

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a standard Excel formula to copy values based on cell color?

No, Excel does not have a built-in worksheet function to read or evaluate cell fill colors. You must use a VBA macro or a third-party add-in to achieve this functionality.

Why am I getting an error when trying to run the VBA macro?

This is often caused by macro security settings. Your Excel Trust Center settings might be configured to disable macros, or your company's IT policies might be actively blocking them for security reasons.

Does this VBA macro affect the entire workbook or just the current sheet?

The suggested macro operates on the UsedRange of the active worksheet. It will only copy values to colored cells within the specific sheet where the macro is currently being executed.

Excel & Data Problems