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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Smart Ways to Use Conditional Formatting for Multiple Rows Independently in Excel
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.
Similar Readings:
- How to Apply Conditional Formatting to Multiple Rows (5 Ways)
- Excel Alternating Row Color with Conditional Formatting [Video]
- Apply Conditional Formatting to Each Row Individually: 3 Tips
- How to Highlight Row Using Conditional Formatting (9 Methods)
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.
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. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
