Apply conditional formatting for each row in Excel
Conditional Formatting in Excel lets you automatically format cells based on their values or specific rules, making it easier to visually analyze data. Sometimes, you may need to highlight each row according to custom logic, as shown in the example screenshot below. Manually setting identical rules for every row can be time-consuming and tedious, especially with larger datasets. Fortunately, there are various approaches that streamline this process and help maintain accuracy and consistency.
- Method A Change the Applies to in Conditional Formatting Rules Manager
- Method B Drag the AutoFill handle
- Method C Use VBA Code for Dynamic Conditional Formatting
Suppose you want to fill the cells' background color if the value in column A of a row is greater than the corresponding value in column B of the same row (e.g., if A2 > B2, highlight A2:B2; if A3 > B3, highlight A3:B3, and so on through the entire data range). This technique avoids repeating rule definitions for every row, letting you expand one conditional rule to a broader range efficiently.
1. First, apply conditional formatting to the starting row. Select cells A2:B2, then click Home > Conditional Formatting > New Rule. (Tip: If you already have rules for this row, skip ahead to step4.)
2. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format in the Select a Rule Type section. Enter the formula =$A2>$B2 in the box labeled Format values where this formula is true. This formula ensures that formatting is applied only when the condition is met on a row-by-row basis.
3. Click the Format button to open the Format Cells dialog. Select a format style as needed, such as a background fill color. Click OK twice to confirm and close all dialog boxes.
At this stage, the conditional formatting is applied just to row A2:B2.
4. With A2:B2 still selected, click Home > Conditional Formatting > Manage Rules.
5. In the Conditional Formatting Rules Manager dialog, locate the rule applied to A2:B2. Update the Applies to range to your desired row range (for example, change from =$A$2:$B$2 to =$A$2:$B$9) and click OK to apply.
This conditional formatting rule is now assigned to each row in the chosen range. Each row's formatting is determined by its individual values, offering an efficient solution for large tables.
Additional tips: When editing the formula, ensure that row references (such as $A2 and $B2) are used rather than absolute references (like $A$2), so the logic aligns correctly for each row. If you encounter formatting not applying as expected, double-check your formula and Applies to range to ensure accuracy.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
If you are using Excel 2013 or later, you can replicate conditional formatting rules to adjacent rows using the AutoFill handle. This approach is particularly useful when you want to extend complex formatting logic down your table quickly.
Suppose cells in row A2:B2 are already set up with two conditional formatting rules: one fills red if A2 > B2 and the other fills green if A2 < B2.
Now, you'd like to apply the same pair of rules to rows A3:B9.
1. Begin by assigning the conditional formatting rules to row A2:B2. If rules exist already, skip to step4. Select A2:B2, choose Home > Conditional Formatting > New Rule.
2. In the New Formatting Rule dialog, opt for Use a formula to determine which cells to format. Type =$A2>$B2 (for the red fill) into the formula box. Then click Format, select a red fill under the Fill tab, and confirm.
3. Keep A2:B2 selected, open the New Formatting Rule dialog again. Choose Use a formula to determine which cells to format, then enter =$A2<$B2 (for the green fill) this time. Set the desired formatting (green fill) and confirm.
4. Next, use the AutoFill handle (small square at the bottom right corner of the selected cell range) to drag down and cover the desired adjacent rows (A3:B9). When prompted by Auto Fill Options, select Fill Formatting Only so only the formatting, not the data, is copied.
Usage notes: The AutoFill handle method works best when your formulas use relative references. If your rules use direct cell addresses, they may not propagate correctly to other rows.
Troubleshooting: If formatting copies incorrectly, check whether "Fill Formatting Only" was selected, and verify each row has the intended rule logic.
In complex situations that involve numerous variable rules or dynamically changing ranges, manual methods may be impractical. VBA can be used to apply or customize conditional formatting automatically for each row, especially in cases where you have large datasets or complex multi-condition logic. VBA enables you to apply sophisticated formatting with just a few clicks and allows easy modifications for future needs.
1. To insert VBA code, click Developer on the Ribbon, then choose Visual Basic. In the Visual Basic for Applications (VBA) window, click Insert > Module to open a blank module page.
2. Copy and paste the following VBA code into the module. This example demonstrates how to use VBA to apply a custom conditional formatting rule to each row, highlighting cells in columns A and B if A is greater than B across the designated range:
Sub DynamicRowConditionalFormatting()
Dim WorkRng As Range
Dim cell As Range
Dim xTitleId As String
Dim fmtRange As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.InputBox("Select range to apply formatting:", xTitleId, Selection.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
WorkRng.FormatConditions.Delete ' Remove existing formatting
For Each cell In WorkRng.Columns(1).Cells
If cell.Row > 1 Then ' Adjust for your header row if needed
If cell.Value > cell.Offset(0, 1).Value Then
If fmtRange Is Nothing Then
Set fmtRange = Range(cell, cell.Offset(0, 1))
Else
Set fmtRange = Union(fmtRange, Range(cell, cell.Offset(0, 1)))
End If
End If
End If
Next cell
If Not fmtRange Is Nothing Then
fmtRange.Interior.Color = vbYellow ' Custom formatting: Yellow fill for cells where A > B
End If
End Sub3. To run the code, click 
in the VBA window. A dialog will appear prompting you to select the range to which you want to apply the formatting (for example, select A2:B9). When the code runs, it will highlight all the cells in columns A and B of each row where the value in column A is greater than the value in column B with a yellow background fill.
Customizing: You can adjust the code to handle different columns or more complex logic and to apply other formatting types, such as font color or borders. Modify cell.Value > cell.Offset(0,1).Value as needed for your criteria.
Precautions: Ensure you select the correct data range, and remember the code clears any previous conditional formatting. You can change vbYellow to other color codes for different highlight styles. Always back up your workbook before running VBA on important data.
Troubleshooting: If you see no formatting, verify your selected range and ensure your data does not contain empty or non-numeric values in the relevant columns.
Sample File
Count/sum cells by colors with conditional formatting in Excel
Now this tutorial will tell you some handy and easy methods to quickly count or sum the cells by color with conditional formatting in Excel.
create a chart with conditional formatting in Excel
For example, you have a score table of a class, and you want to create a chart to color scores in different ranges, here this tutorial will introduce the method on solving this job.
Conditional formatting stacked bar chart in Excel
This tutorial, it introduces how to create conditional formatting stacked bar chart as below screenshot shown step by step in Excel.
Conditional formatting rows or cells if two columns equal in Excel
In this article, I introduce the method on conditional formatting rows or cells if two columns equal in Excel.
Search and highlight search results in Excel
In Excel, you can use the Find and Replace function to find a specific value, but do you know how to highlight the search results after searching? In this article, I introduce two different ways to help you search and highlight search results at the meanwhile in Excel.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
