Formatting Issues

Fix Excel Conditional Formatting That Does Not Highlight a Row

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 869 views

Question details

The user needs to highlight an entire row based on a status selected from a data validation list.

Fix Excel Conditional Formatting That Does Not Highlight a Row
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has a status column with a drop-down list, and the user wants each row formatted according to its selected status.
Observed behavior
The conditional formatting rule does not highlight the intended row.
Before you start

Identify the status column, the first data row, and the columns you want to highlight. The example below uses column J for status and A2:J100 as the range to format.

Solution 1Recommended

Correct the Formula and Applied Range in Excel

Use a formula that locks the status column while allowing the row number to change for each row.

1
Open the rule manager

On the Home tab, select Conditional Formatting > Manage Rules. Choose the existing rule and select Edit Rule, or create a new rule.

2
Enter the formula

Choose the option to use a formula to determine which cells to format. For a status in column J and data starting on row 2, enter =$J2="Open".

3
Choose a format

Select Format, choose a fill color, and confirm your selection.

4
Set the full row range

In Manage Rules, set Applies to to =$A$2:$J$100, or adjust the columns and last row to match your data. The first row of the range must match the row number in the formula.

5
Test the rule

Select Open from a status drop-down in column J and confirm that the corresponding row is highlighted.

Correct the Formula and Applied Range in Excel
Check the status text: The formula checks for Open. If the rule does not trigger, inspect the selected value for extra spaces or a different label.
Highlight rows in WPS Spreadsheets

Highlight Status Rows in WPS Spreadsheets

Use a formula-based conditional formatting rule in WPS Spreadsheets to highlight each row according to its status.

  1. 1. Open the workbook: Open the spreadsheet in WPS Spreadsheets and identify the status column and first data row.
  2. 2. Select the row range: Select the cells to highlight, such as A2:J100, starting with the first data row.
  3. 3. Create a formula rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula. Enter =$J2="Open" for the example range.
  4. 4. Set the formatting: Choose a fill color, confirm the rule, and select Open in a status cell to test the row highlight.
Highlight entire rows based on a status columnOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use $J2 instead of $J$2?

The dollar sign locks the status column J, while the row number changes for each row in the applied range. Using $J$2 would make every row check only cell J2.

What if my data starts on row 5?

Use row 5 in the formula, such as =$J5="Open", and make sure the Applies to range also begins on row 5, for example =$A$5:$J$100.

How do I highlight columns beyond the status column?

In Home > Conditional Formatting > Manage Rules, extend the Applies to range. For example, use =$A$2:$N$100 to highlight columns A through N while the formula still checks column J.

Why is the row not highlighted when I select Open?

In Manage Rules, check that the formula references the correct status column and first data row. Also check the selected status for extra spaces or a label that differs from Open.