Office Settings & Configuration

Use Conditional Formatting with a Drop-Down List in WPS Spreadsheets

Chanuka Geekiyanage Nov 13, 2026 869 views

Question details

The user wants to apply formula-based conditional formatting in WPS Spreadsheets so the column selected from a drop-down list turns green and all other columns turn gray automatically, without using VBA.

Use Conditional Formatting with a Drop-Down List in WPS Spreadsheets

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user has a workbook with a drop-down list and multiple columns; the selected value should determine which column is highlighted green.
Observed behavior: Columns do not change color dynamically when the drop-down value changes, or the user needs a non-VBA conditional formatting rule to achieve this.

Before you start

Create or identify the drop-down cell and make sure the column headers exactly match the allowed drop-down values. Keep the drop-down cell in a fixed location, such as A1, because the formulas will use an absolute reference to it.

Solution 1Recommended

Apply formula-based conditional formatting to turn the selected column green and all other columns gray

Use this solution when you have a drop-down list that selects a column and you want the matching column to become green while all other columns turn gray automatically, with no VBA.

Conditional formatting evaluates each cell in the selected range. A formula with a relative column reference, such as B$1=$A$1, compares each column header with the drop-down value in $A$1, so the formatting updates automatically when the drop-down changes.

Prepare the drop-down cell and column headers

Place the drop-down list in a fixed cell, such as A1, and make sure each column you want to highlight has a unique header that exactly matches one of the drop-down values.

Select all columns to format

Select the full range that contains the columns you want to format, for example B2:G100. To highlight an entire column, click the column letter and drag across the other columns.

Create the green matching rule

Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =B$1=$A$1, adjust B to the first column of the selected range, click Format, choose a green fill, and confirm.

Create the gray non-matching rule

With the same range selected, create another New Rule > Use a formula to determine which cells to format. Enter =B$1<>$A$1, set the fill to gray, and confirm.

Check rule priority and test the drop-down

Click Home > Conditional Formatting > Manage Rules, make sure the green rule is above the gray rule or enable Stop If True for the green rule, then click OK and test by changing the drop-down selection.

Apply formula-based conditional formatting to turn the selected column green and all other columns gray

Dynamic column highlighting works without VBA: When the selection changes, the matching column turns green and all other columns remain gray. If you are formatting a single column with fixed drop-down values, you can instead use =$A$1="YourDropdownValue" and create one green rule per allowed value.

WPS Spreadsheets

Apply dynamic drop-down column formatting in WPS Spreadsheets

Use formula-based conditional formatting in WPS Spreadsheets to turn the column selected in a drop-down list green and all other columns gray whenever the value changes, with no VBA or complex macros.

1. Open your workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the workbook that contains the drop-down list and the columns to format.
2. Select the data range: Select all cells or columns you want to change color, such as B2:G100.
3. Create the green conditional formatting rule: Go to Home > Conditional Formatting > New Rule > Use a formula, enter =B$1=$A$1, set a green fill, and confirm.
4. Create the gray rule: Repeat the process and enter =B$1<>$A$1, set a gray fill, and click OK.
5. Test the drop-down: Change the drop-down selection and verify that the matching column becomes green while the rest become gray.

Formula-based conditional formatting updates instantly when the drop-down value changesNo VBA or macros required to highlight the selected columnOpens and saves Microsoft Excel XLSX files while preserving conditional formatting rulesFree and lightweight WPS Office tools with a familiar spreadsheet interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I highlight an entire column based on a drop-down value without VBA?

Yes. Apply formula-based conditional formatting to the range and use a relative column reference such as =B$1=$A$1, where A1 is the drop-down cell and B1 is the first column header.

Why does my conditional formatting rule not update when I change the drop-down?

Check that the formula uses an absolute reference for the drop-down cell, such as $A$1, and a relative column reference, such as B$1. Also make sure the rule is applied to the correct range and the drop-down values exactly match the column headers.

How do I make all other columns gray while only the selected column is green?

Create a second conditional formatting rule with the formula =B$1<>$A$1, set the fill to gray, and apply it to the same range. Then confirm the green rule is above the gray rule in Manage Rules or enable Stop If True.

Can WPS Spreadsheets open Excel workbooks that use these conditional formatting rules?

Yes. WPS Spreadsheets supports Microsoft Excel XLSX files and preserves formula-based conditional formatting rules, so you can open and edit the workbook without losing the formatting.

What should I do if my drop-down list values contain spaces or punctuation?

Use the cell reference comparison =B$1=$A$1 instead of typing the value directly into the formula. If you type a fixed value, enclose it in double quotes exactly as it appears in the drop-down list.

Office App Problems