How to Apply Conditional Formatting Based on Another Cell’s Value in Excel

Conditional formatting in Microsoft Excel is a fantastic way to visually highlight data, such as turning a cell red if its value drops below zero. However, standard conditional formatting rules apply the formatting based on the value inside the formatted cell itself. What if you want to format an entire row of data based on the status in a single column? Or what if you want a project title in column A to turn green only when the status dropdown in column B says “Complete”? To achieve this, you must use a custom formula to drive your conditional formatting rule.

Understanding Relative vs. Absolute References in Formulas

The key to making conditional formatting work across multiple cells is understanding how Excel uses dollar signs ($) to lock column references. When you write a formula for conditional formatting, Excel evaluates that formula for every single cell in your selected range. If you want the formatting of column A to depend on column B, you must lock the column reference to B using a dollar sign (e.g., $B1). If you fail to lock it, Excel will shift the reference as it moves across the row, evaluating column C for column B, column D for column C, and the formatting will break.

Formatting a Cell Based on the Value of an Adjacent Cell

Let’s assume you have a list of tasks. The task name is in Column A, and the status (“In Progress”, “Complete”, “Delayed”) is in Column B. You want the task name in Column A to turn green when Column B says “Complete”.

1. Select the range of cells you want to format. In this case, select the task names in Column A (e.g., A2:A100).
2. On the Home tab of the ribbon, click Conditional Formatting in the Styles group.
3. Select New Rule… from the dropdown menu.
4. In the New Formatting Rule dialogue, click the last option in the list: Use a formula to determine which cells to format.
5. In the “Format values where this formula is true” box, enter the following formula: =$B2="Complete"
6. Click the Format… button.
7. Go to the Fill tab and select a green colour. You can also change the font colour on the Font tab if desired.
8. Click OK to close the Format Cells dialogue, and click OK again to apply the rule.

Notice that the formula references row 2 ($B2) because row 2 was the top-left cell of the range we selected (A2:A100). Excel automatically adapts the row number for the rest of the cells in the range, but the $B ensures it always looks at Column B.

Formatting an Entire Row Based on a Single Cell

If you want the entire row—the task name, the status, the due date, and the assignee—to turn green when the status is “Complete”, the process is nearly identical, but your initial selection changes.

1. Select the entire data table, excluding the header row (e.g., select A2:D100).
2. Click Conditional Formatting > New Rule…
3. Select Use a formula to determine which cells to format.
4. Enter the exact same formula as before: =$B2="Complete"
5. Click Format…, choose your green fill colour, and click OK.

Because the column reference ($B) is locked, when Excel evaluates cell C2, it still looks at B2. When it evaluates D2, it still looks at B2. Therefore, the entire row shares the same formatting trigger.

Troubleshooting Common Errors

If your formatting is appearing on the wrong rows (for example, the formatting for row 2 appears on row 3), it means your formula row number did not match the top row of your selected range. If you selected A2:D100 but wrote =$B1="Complete" or =$B3="Complete", the formatting will be offset by one row. Always ensure the row number in your custom formula perfectly matches the very first row of your highlighted selection.