How to Change Font Color in WPS Spreadsheets When Two Cells Contain Specific Values
Question details
The user wants to apply conditional formatting in WPS Spreadsheets so the font color changes when two specified values appear in different columns on the same row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a spreadsheet and needs to automatically highlight rows based on matching text in two different columns using conditional formatting.
- Observed behavior
- The font color does not change automatically when the two target values appear, so the user needs a conditional formatting rule.
Open the workbook and confirm the two comparison columns and the exact text values to match. Make sure the active cell in the selected range is in the first row you want the rule to evaluate.
Use a Conditional Formatting Formula to Change Font Color for Matching Rows
Use this method when you need WPS Spreadsheets to automatically change font color only when specific values exist in two columns on the same row.
Use a formula in a conditional formatting rule so WPS Spreadsheets evaluates each row relative to the active cell. Dollar signs lock the column references, while relative row numbers let the rule adjust for every selected row.
Open your workbook in WPS Spreadsheets and select the target range, such as A1:H100, making sure the active cell is in row 1.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, enter =($A1="Blue")*($H1="Rabbit"), replacing Blue and Rabbit with your target text values.
Click Format, open the Font tab, choose the desired font color under Color, and click OK twice to apply the rule.
Use an AND Formula for the Same Two-Cell Conditional Formatting Rule
Use this equivalent formula if you prefer a more readable Excel-style AND statement for matching two values in the same row.
Highlight Matching Rows Automatically with WPS Spreadsheets
WPS Spreadsheets lets you apply formula-based conditional formatting to change font color when two cells in the same row contain specific values, using Excel-compatible formulas and a familiar interface.
- 1. Open WPS Office: Launch WPS Office, open WPS Spreadsheets, and open the Excel-compatible workbook you need to format.
- 2. Select the target range: Highlight the rows or columns that contain the two values to evaluate, starting from the row that matches the formula row reference.
- 3. Create a formula rule: Click Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter =($A1="Blue")*($H1="Rabbit").
- 4. Apply font color: Click Format, open the Font tab, choose the desired font color from Color, and click OK to apply the rule.
- 5. Save the workbook: Save the file to keep the conditional formatting rule active for future edits.
Frequently Asked Questions
Why does my conditional formatting rule not highlight the correct rows?
Check that the row number in the formula matches the active cell's row. If your selection starts at row 2, use =($A2="Blue")*($H2="Rabbit") and keep the row numbers relative instead of locking them.
Can I use this rule on an entire column?
Yes. Select the entire target range, then base the formula on the first row of the selection, such as =($A1="Blue")*($H1="Rabbit"), and WPS Spreadsheets adjusts the row reference automatically.
Does the formula distinguish between uppercase and lowercase text?
Conditional formatting formulas in WPS Spreadsheets are generally case-insensitive for text comparison, so "Blue" and "blue" are treated the same. Use EXACT if case-sensitive matching is required.
How do I change the rule if my values are in different columns?
Replace the column letters and comparison values in the formula. For example, if values are in column C and column N, use =($C1="Red")*($N1="Fox").
