Conditional Formatting on Multiple Rows Independently in Excel

Conditional Formatting is one of the essential tools in Microsoft Excel. Analyzing data, creating insights, and other things that we can do using this. It can save you a lot of time and relieve the load of immense work pressure. We use conditional formatting mostly for data that depend on each other. But, in this tutorial, you will learn to use conditional formatting for multiple rows independently in Excel.

Here is an overview of applying Conditional Formatting for multiple rows independently. Based on the applied conditions, the cell gets colored.

Use Conditional Formatting for Multiple Rows Independently in Excel: 2 Smart Ways

Now, in the upcoming sections, we are providing you with two simple but effective methods to apply conditional formatting for multiple rows in Excel. Remember, you have to set the conditions first. Then you will apply these to your rows. The VBA code is just for copying the conditional formatting across all the rows of your selection.

To demonstrate this, we are going to use the following dataset:

Here, we have a dataset consisting of some sales persons’ names and their selling amount across the month of January, February, and March. Our goal is to apply conditional formatting for each month independently by using various color formats.

1. Use of the Conditional Formatting Command for Multiple Rows

The most important part is to apply conditional formatting using the Conditional Formatting command from the Home tab in Excel. This is easy to use. You have to set the condition and color format.

Here, we are using the LARGE function to solve our problem. The LARGE function will return the nth largest value of the row. We will set the color format after that.

📌 Steps

① First, select the range of cells D5:F10.

② Now, from the Home tab, go to Conditional Formatting > Manage Rules.

③ After that, the Conditional Formatting Rules Manager dialog box will open. Click on New Rule.

④ In the New Formatting Rule dialog box, select Use a formula to determine which cells to format. Then, type the following formula :

=D5=LARGE($D5:$F5,1)

This formula will return the 1st largest value of the three months. Then click on Format.

⑤ From the Format Cells dialog box, select Fill menu. Choose any fill colors. Then click OK.

⑥ Now, you have set your formula and fill color. Now, click on OK.

⑦ Now, mark the Stop IF True checkbox. This is important. It will make sure your formula will work only for rows independently. Then click on New Rule to add more formulas.

⑧ Now, again create two more rules like the previous one. These two rules will return 2nd and 3rd highest values respectively.

The formula for 2nd highest value:

=D5=LARGE($D5:$F5,2)

The formula for 3rd highest value:

=D5=LARGE($D5:$F5,3)

⑨ Finally, you have set all the formats and formulas. Remember to mark the checkboxes.

⑩ After that, click on OK.

As you can see are successful in applying conditional formatting on multiple rows independently.

2. VBA codes for Multiple Rows Independently in Excel

You can use the VBA codes to use conditional formatting. This method is to copy the format across all the rows. First, You have to apply the rules. Then the VBA codes will copy the formatting and paste it to all the rows.

Remember, it will automatically create a rule for every row. That’s why this is different from the previous one.

📌 Steps

① First, select the range of cells D5:F5

② Now, from the Home tab, go to Conditional Formatting > Manage Rules.

③ After that, the Conditional Formatting Rules Manager dialog box will open. Click on New Rule.

④ In the New Formatting Rule dialog box, select Use a formula to determine which cells to format. Then, type the following formula :

=D5=LARGE($D5:$F5,1)

This formula will return the 1st largest value of the three months. Then click on Format.

⑤ From the Format Cells dialog box, select Fill menu. Choose any fill colors. Then click OK.

⑥ Now, you have set your formula and fill color. Now, click on OK.

⑦ Now, mark the Stop IF True checkbox. This is important. It will make sure your formula will work only for rows independently. Then click on New Rule to add more formulas.

⑧ Now, again create two more rules like the previous one. These two rules will return 2nd and 3rd highest values respectively.

The formula for 2nd highest value:

=D5=LARGE($D5:$F5,2)

The formula for 3rd highest value:

=D5=LARGE($D5:$F5,3)

⑨ Finally, you have set all the formats and formulas. Remember to mark the checkboxes.

⑩ After that, click on OK. It will format the first row with 3 color scale

⑪ Now, press Alt+F11 on your keyboard to open the VBA Editor. Select Insert > Module.

⑫ Then, type the following code:

Sub format_all_rows()
Dim rng As Range
Dim r As Long
Set rng = Selection
rng.Rows(1).Copy
For r = 2 To rng.Rows.Count
  rng.Rows(r).PasteSpecial Paste:=xlPasteFormats
Next r
End Sub

⑬ Now, save the file. After that, select the range of cells D5:F10.

⑭ Then, press Alt+F8 to open the Macro dialog box. Select format_all_rows.

⑮ After that, click on Run.

As you can see, we have successfully applied conditional formatting on multiple rows independently. Our methods are evaluating each row independently.

💬 Things to Remember

✎ To implement these methods, always mark the “Stop If True” checkbox. It will ignore other rules when our data meet the conditions.

✎ This VBA code will generate the same rule for each row. So, if your dataset is large, it may slow down your process.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conditional Formatting on Multiple Rows Independently.xlsm

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge about conditional formatting on multiple rows independently in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section.

<< Go Back to Conditional Formatting Rows | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!