How to Change an Excel Cell's Font Color Based on Another Cell
Question details
The user wants to automatically change the text color in a target cell (e.g., A10) whenever the text in a reference cell (e.g., A1) is formatted with a specific color like red.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating spreadsheet formatting so that dependent cells visually update their font color based on the status or formatting of another reference cell.
- Observed behavior
- Standard Excel functions cannot directly test or detect a cell's font color. Directly triggering a formatting change based purely on the manual color formatting of another cell requires VBA, though formula-based Conditional Formatting offers a more robust workaround.
Determine why the original cell is colored red. If it was colored manually, you will need a VBA macro; if it is colored dynamically based on its data value, you can easily use Conditional Formatting for the target cell.
Use Conditional Formatting Based on Cell Values
Since Excel cannot natively read font colors using standard formulas, the best practice is to apply a Conditional Formatting rule to the target cell based on the underlying value of the reference cell.
This method avoids complex coding by using the actual data driving the color change. For example, if cell A1 turns red because its value is greater than 100, you can set cell A10 to turn red using that exact same mathematical condition.
Click on the cell or range of cells you want to change the font color of (e.g., cell A10).
Navigate to the Home tab on the Excel ribbon, click the 'Conditional Formatting' button, and choose 'New Rule' from the dropdown menu.
In the New Formatting Rule dialog box, click on 'Use a formula to determine which cells to format'.
In the formula bar, type the condition that checks the value of the reference cell. For example, type '=A1>100' or '=A1="Error"'.
Click the 'Format' button, go to the 'Font' tab, select the color red from the color palette, and click 'OK' twice to apply the formatting rule.
Use a VBA Macro to Match Font Colors
If the original cell's font color is changed manually by a user (and not by a value-driven rule), you must use a VBA macro to detect and replicate that exact color.
Easily Manage Conditional Formatting with WPS Spreadsheet
WPS Office provides a highly compatible, user-friendly Spreadsheet application that fully supports advanced conditional formatting and VBA macros. Easily format your cells based on complex logical criteria just like in Microsoft Excel.
- 1. Open your spreadsheet in WPS Office: Launch WPS Office and open your .xlsx file in the WPS Spreadsheet application.
- 2. Access Conditional Formatting: Navigate to the Home tab and click on the Conditional Formatting icon located in the main toolbar.
- 3. Set up a new rule: Select 'New Rule', choose the formula option, input your logical condition linking the cells, and customize your desired text color.
Frequently Asked Questions
Is there an Excel function that returns a cell's font color?
No, standard Excel functions do not extract or read cell formatting properties like font color or background color. You must use VBA macros or legacy Excel 4.0 macro functions to retrieve color index values.
Why isn't my conditional formatting rule updating automatically?
If your conditional formatting is based on a formula, ensure your calculation options are set to 'Automatic' in the Formulas tab. If you used VBA to match colors, remember that changing a font color manually does not trigger a standard worksheet recalculation.
Can I copy my new conditional formatting rule to other cells?
Yes. You can use the Format Painter tool on the Home tab to quickly copy a conditional formatting rule from your target cell and apply it to a larger range of cells.
