How to Change Adjacent Cell Color from a Yes or No Dropdown in WPS Office Spreadsheets
Question details
The user needs to change the fill color of an adjacent cell automatically when a Yes/No data-validation dropdown cell displays No.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a Yes/No dropdown in one cell and wants the neighboring cell to be highlighted based on the selected value.
- Observed behavior
- The adjacent cell does not change fill color when the dropdown value is No unless conditional formatting is applied.
Make sure the dropdown list is already created with Data Validation and that you know the exact cell reference, such as D7, that contains the Yes or No value. Select the adjacent cell or range you want to highlight before creating the conditional formatting rule.
Apply a Conditional Formatting Rule with a Custom Formula to Highlight the Adjacent Cell
Use this method to automatically recolor the adjacent cell whenever the Yes/No dropdown shows No, without changing the dropdown value or other cell contents.
Conditional formatting evaluates the formula for each cell in the selected range. Use an absolute reference for the dropdown cell, such as $D$7, so the rule always checks that cell instead of shifting the reference.
Select the adjacent cell or range that should change color—for example, select row 8 or the specific cell next to the dropdown at D7.
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" and enter =$D$7="No" in the formula box. Adjust D7 to match the actual dropdown cell.
Click Format, open the Fill tab, choose the highlight color you want, and click OK to apply the rule.
Change Adjacent Cell Color from a Yes or No Dropdown in WPS Office Spreadsheets
Create a conditional formatting rule with =$D$7="No" in WPS Office Spreadsheets to automatically recolor the adjacent cell whenever the Yes/No dropdown value changes to No, keeping the dropdown data unchanged.
- 1. Open the workbook: Open the workbook in WPS Office Spreadsheets and locate the Yes/No dropdown cell.
- 2. Select the target range: Select the adjacent cell or range that should change fill color.
- 3. Create a new conditional formatting rule: Click the Home tab, then click Conditional Formatting and select New Rule.
- 4. Add the formula and format: Choose the formula-based rule, enter =$D$7="No", click Format, pick a fill color, and confirm OK.
Frequently Asked Questions
Can I highlight an entire row instead of only the adjacent cell?
Yes. Select the entire row or range, then use a formula that locks the dropdown column and row, such as =$D$7="No", and the rule will apply to every selected cell.
Why does the conditional formatting formula need $ signs?
The $ signs create an absolute reference so every cell in the selected range checks the same dropdown cell. Without them, the reference shifts by row or column and the highlighting may not work as expected.
Will conditional formatting change the Yes/No dropdown value?
No. Conditional formatting only changes the cell appearance. The dropdown value and cell contents remain unchanged.
Can I use other values besides Yes and No?
Yes. Replace the text in the formula with the exact dropdown value, for example =$D$7="Pending". Make sure the spelling and capitalization match the data validation list.
Does this formula-based rule work in Microsoft Excel files opened in WPS Office?
Yes. WPS Office Spreadsheets supports Excel .xlsx and .xls conditional formatting rules, so the same formula-based rule works when the file is opened in WPS Office.
