VBA & Macro Problems

How to Copy Cells with Red Font to Another Column Using VBA in Excel

Camila Milosovich Oct 20, 2026 868 views

Question details

The user needs to examine rows 3 through 200 in a specific column and copy only the numeric values that display with a red font to a second column. The red font is applied dynamically via conditional formatting.

How to Copy Cells with Red Font to Another Column Using VBA

Product: Excel
Device & OS: not provided
Scenario: Extracting and duplicating data to a different column based on its visual text color that is generated by conditional formatting rules.
Observed behavior: Standard worksheet formulas and standard VBA Font.Color checks cannot reliably detect colors applied by conditional formatting, requiring a specific VBA approach to read the displayed format.

Before you start

Before running any macro, ensure you have saved a backup copy of your workbook. Macros cannot be easily undone, and clearing previous results in the destination column will permanently overwrite existing data.

Solution 1Recommended

Use the VBA DisplayFormat Property

The most reliable way to detect font color applied by conditional formatting is by checking the DisplayFormat.Font.Color property in a VBA macro.

Because conditional formatting overrides the cell's standard format, traditional VBA checks like 'Font.Color' will only return the base formatting, completely ignoring the conditional rule. The 'DisplayFormat' property evaluates the cell exactly as it appears visually on your screen.

Open the VBA Editor

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

Insert a New Module

Click 'Insert' in the top menu bar and select 'Module' to create a blank workspace for your macro.

Write the DisplayFormat Loop

Write a VBA loop that iterates through rows 3 to 200. Clear the destination column first. Inside the loop, use an If statement to check: If Cells(i, 1).DisplayFormat.Font.Color = vbRed Then, and copy the value to the destination column (e.g., column B).

Run the Macro

Close the VBA editor, press ALT + F8 to open the Macro dialog, select your newly created macro, and click 'Run'.

Version and UDF Limitations: The DisplayFormat property is available in Excel 2010 and later. Note that Microsoft explicitly prevents DisplayFormat from working inside User-Defined Functions (UDFs) called directly from worksheet cells; it must be executed within a standard macro.

Solution 2

Replicate the Conditional Formatting Logic in VBA

If you want to avoid using DisplayFormat or are using an older Excel version, you can code the exact condition that triggers the red font directly into your macro.

Automate with WPS Office

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides excellent support for VBA macros, allowing you to run your scripts, evaluate conditional formatting via DisplayFormat, and automate data extraction without rewriting your Excel code.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your conditionally formatted data.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
3. Open the VBA Editor: Click the 'Visual Basic' icon to open the VBA Editor and paste your DisplayFormat script.
4. Run Your Script: Click the 'Run' button (or press F5) to execute the macro and successfully copy your cells.

Fully compatible with Microsoft Excel VBA syntax and propertiesSeamless execution of DisplayFormat and conditional logic macrosLightweight and fast performance for large dataset processingFree to use with a highly familiar ribbon interface

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the CELL("color") formula detect conditional formatting colors?

The built-in CELL("color") function only detects colors applied through custom number formatting (like accounting formats for negative numbers). It cannot read colors dynamically generated by conditional formatting rules.

Can I use DisplayFormat in a custom worksheet formula?

No, Microsoft explicitly prevents the DisplayFormat property from working inside User-Defined Functions (UDFs) that are evaluated directly in worksheet cells. It only works when executed from a standard VBA macro subroutine.

How do I clear the destination column before copying new data in VBA?

You can add a line at the beginning of your macro before the loop starts, such as 'Range("B3:B200").ClearContents' (assuming column B is your destination), to ensure any old data is removed before new values are pasted.

What if the red font was applied manually instead of conditionally?

If the font was changed manually using the font color picker on the ribbon, you can simplify your macro by checking the standard 'Font.Color' property (e.g., Cells(i, 1).Font.Color = vbRed) instead of using 'DisplayFormat.Font.Color'.

Excel & Data Problems