Apply Conditional Formatting for Each Row in Excel

Conditional formatting, as we all know, will generate a rule that will decide which cells get formatted and how they should be formatted. It's possible that you'll wish to use the conditional formatting for per row at some point.

Using conditional formatting, for instance, you might want to fill the background color of the cells in this area with a different color if cell A2 is greater than cell B2, A3 is greater than cell B3, and so on.

Let’s understand step by step with an example.

Step 1

At first, we must create a sample data, as shown in the following screenshot.

Step 2

In the first step, you need to apply the conditional formatting to the range A2:B2. After making your selection in A2:B2, go to the Home tab, then click Conditional Formatting and select New Rule. Refer the screenshot below.

Step 3:

In the New Formatting Rule dialogue box, select Use a formula to determine which cells to format from the section titled Select a Rule Type. Next, in the textbox located under Format values where this formula is true, type the formula "=$A2>$B2".

Formula

=$A2>$B2

Step 4

Click the Format button to open the Format Cells window. Under the Fill section, choose any colour of your choice and click the OK button.

Now, click the OK button to close the New formatting Rule window.

Step 5

Now, the conditional formatting is applied to rows A2:B2.

In the next step, select the data range from A2:B9 and choose conditional formatting option under Home tab and choose manage rules.

Step 6

Find the rule you applied to A2:B9 in the Conditional Formatting Rules Manager window, modify the range as needed in the Applies to section, and click OK.

Step 7:

Now, for each row in the new range, this conditional formatting rule will be implemented. Refer to the below screenshot.

Conclusion

In this tutorial, we explained how you can apply conditional formatting to a data range based on the custom conditions stated in conditional formatting rules.