How to Apply Conditional Formatting Based on Another Cell's Color in Spreadsheet
Question details
The user wants to apply conditional formatting to a target cell based on whether another specific reference cell is colored red.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Trying to trigger a format change in a target cell when a reference cell changes color due to its own conditional formatting rule.
- Observed behavior
- Spreadsheet formulas cannot directly read or evaluate the fill color of a cell, requiring a workaround that reuses the source cell's underlying logical conditions.
Identify the exact logical condition or formula that causes your source cell to turn red. Since spreadsheet formulas cannot detect cell colors directly, you will need to reuse this underlying mathematical logic.
Reusing Logical Conditions with Formula-Based Conditional Formatting
Apply the original condition that triggers the red color in the source cell to format your new target cell.
Spreadsheet software does not have a built-in function to read cell colors directly within conditional formatting. However, you can easily mirror the formatting by applying the exact same logical rule to the target cell.
When dealing with complex rules, you can combine conditions. Use multiplication to represent an AND condition, and addition to represent an OR condition.
Check the existing conditional formatting rules of the source cell to see the exact logical condition that makes it red (for example, A1>100).
Highlight the cell or range of cells where you want the new conditional formatting to appear.
Navigate to Home > Conditional Formatting > New Rule, and select the option 'Use a formula to determine which cells to format'.
Input the logical condition from the source cell. If combining multiple conditions, use multiplication for AND logic and addition for OR logic. For example: =(Condition1)*(Condition2)+(Condition3).
Click the Format button, choose the red fill color (or any desired format), and click OK to apply the rule to your target cell.
Easily Manage Conditional Formatting Rules with WPS Spreadsheet
WPS Office Spreadsheet provides a highly intuitive interface to create, manage, and duplicate complex conditional formatting rules based on formulas, ensuring your data analysis is accurate and visually clear.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet file.
- 2. Access Conditional Formatting: Navigate to the Home tab and click on the Conditional Formatting button to reveal the dropdown menu.
- 3. Use the Rule Manager: Select 'Manage Rules' to view all active conditions, seamlessly edit your logical formulas, and apply the exact same logic to multiple target cells.
Frequently Asked Questions
Can a standard spreadsheet formula check if a cell is colored red?
No, standard built-in functions cannot evaluate cell fill colors or font colors. You must evaluate the underlying data or logical condition that caused the color change in the first place.
How do I represent AND/OR logic in conditional formatting formulas?
In conditional formatting formulas, you can represent an AND condition by multiplying the logical statements together (e.g., =(A1>5)*(B1<10)). For an OR condition, add the statements together (e.g., =(A1>5)+(B1<10)).
Will copying with the Format Painter duplicate the condition?
The Format Painter copies both standard formatting and conditional formatting rules. It will apply the same rule to the new cell, but the cell references will adjust relatively unless you lock them with dollar signs ($).
Can I use VBA to check a cell's background color?
Yes, if the cell is manually colored, you can use a VBA macro to read the Interior.Color property. However, reading colors generated by conditional formatting requires more complex VBA (DisplayFormat.Interior.Color) which cannot be used directly inside standard worksheet functions.
