Conditional Formatting on Multiple Rows Independently in Excel

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.


Download Practice Workbook

Download this practice workbook


2 Methods 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:

dataset for conditional formatting on multiple rows independently in excel

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.

select the range of cells to apply conditional formatting

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

select conditional formatting on multiple rows in excel

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

click on new rules

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.

type formula to apply conditional formatting independently in excel

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

format your cells with your fill color

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

rules and formulas to apply conditional formatting in excel

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.

mark the checkbox stop if true

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

Formulas:

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

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

conditional formatting on multiple rows independently another formula

conditional formatting on multiple rows independently in Excel

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

all the formulas and fill color format is set

After that, click on OK.

conditional formatting on multiple rows independently in Excel result

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


Similar Readings:


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

select range of cells to apply conditional formatting in excel

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

select conditional formatting command from home tab

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

click on new rules

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.

type formula to apply conditional formatting independently in excel

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

format your cells with your fill color

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

rules and formulas to apply conditional formatting in excel

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.

formula is set to apply

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

Formulas:

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

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

conditional formatting on multiple rows independently another formula

conditional formatting on multiple rows independently in Excel

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

all the formulas and fill colors are set

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

3 color scale conditional formatting on multiple rows independently in excel formatting

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

insert module for VBA codes

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.

select the range of cells

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

macro dialog box to apply conditional formatting

After that, click on Run.

conditional formatting on multiple rows independently in excel

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.


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo