Fix Excel Conditional Formatting That Does Not Highlight a Row
Question details
The user needs to highlight an entire row based on a status selected from a data validation list.

- 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.
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.
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.
On the Home tab, select Conditional Formatting > Manage Rules. Choose the existing rule and select Edit Rule, or create a new rule.
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".
Select Format, choose a fill color, and confirm your selection.
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.
Select Open from a status drop-down in column J and confirm that the corresponding row is highlighted.

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. Open the workbook: Open the spreadsheet in WPS Spreadsheets and identify the status column and first data row.
- 2. Select the row range: Select the cells to highlight, such as A2:J100, starting with the first data row.
- 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. Set the formatting: Choose a fill color, confirm the rule, and select Open in a status cell to test the row highlight.

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.




