using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

USING IRONXL

How to Highlight Every Other Row in Excel

Updated January 7, 2024

Microsoft Excel offers an inbuilt functionality to highlight rows as per the user's needs. To highlight rows in Excel, you can use the Highlight Cells Action. The highlight rows in Excel feature is a simple way to make the document more interesting to look at. This tool comes in handy for showing the difference between rows of data points. But, highlighting the odd-numbered row in Microsoft Excel can be tricky and not so easy for the beginner. However, it can become very easy for you to shade alternate rows. We will explain it step by step.

Let's get started:

Select the rows you want to format.
Click Home from the main menu and then click on Format as Table.
Select a table style that has alternate row shading from the Table dialog box.
Now, if you want to change the shading from rows to columns, select the table and click on Design from the table style options group. Then, deselect the Banded Rows box from the drop-down menu and select the Banded Columns box.

How to Highlight Every Other Row in Excel, Figure 1: Navigate to Banded Columns feature in Excel Navigate to Banded Columns feature in Excel

If you want to keep your table layout, but don't need its functionality, you can convert it to a range of data. If you're adding color rows/columns and have banding, this won't be replicated automatically. However, you can re-create the formatting by copying rows or columns with alternate formatting with Format Painter.

Use conditional formatting to apply banded rows or columns

You can also use a conditional formatting rule to apply different formatting to specific rows or columns. Here's how we can do it:

On the worksheet, do one of the following:
- If you want to apply formatting on some cells, then select the range of cells or rows. And if you want to apply the formatting to the whole document then select the whole document by pressing Ctrl+A.
Go to Home > Conditional Formatting > New Rule. It will open the formatting rule dialog box.

How to Highlight Every Other Row in Excel, Figure 2: Setup a new rule for Conditional Formatting Setup a new rule for Conditional Formatting

In the "Select a Rule Type" section, select the "Use a formula to determine which cells to format" option.
Write the following formula in the "Edit the Rule Description" section to apply color to alternate rows.

=MOD(ROW(),2)=0

To apply color to alternate columns, type this formula: =MOD(COLUMN(),2)=0.

These formulas determine whether a row or column is even or odd-numbered, and determine the color we want to apply.

How to Highlight Every Other Row in Excel, Figure 3: New Formatting Rule Dialog New Formatting Rule Dialog

Click Format.
In the Format Cells box, click Fill.
Pick a color and click OK.
You can preview your choice under Sample and click OK or pick another color.

By implementing the following steps, you will see the alternate rows highlighted.

IronXL: C# Excel Library

IronXL is a .NET Excel library that provides developers with a set of tools to work with Excel. The library can be used to read, write and modify Excel files and sheets. It can also be used to convert between different file formats like XML, JSON, HTML, and CSV. The IronXL library has been developed in C# programming language and it is open source, which means developers are free to use it in their applications. The IronXL Excel library can be used in any .NET project that needs access to Microsoft Office Excel. Developers can use IronXL in their projects without the need to install Office on their computer or set up a developer license from Microsoft.

The IronXL Excel library provides the following features:

A broad set of functions, including data manipulation, data export, data import and importing from other sources, etc.
Support for all the latest versions of Microsoft Excel.
Support for Excel's most popular file formats (.xlsx).
Support for cell formatting such as text alignment, font size, color, borders, etc.
Ability to control the way that cells are displayed in an Excel workbook (e.g. gridlines).

We can do conditional formatting using IronXL. To use IronXL, you have to install the IronXL library in your C# project. After installing the library, you have to add the IronXL namespace. Write the following line of code on top of your program file:

using IronXL;
using IronXL.Formatting;
using IronXL.Formatting.Enums;
using IronXL.Styles;

using IronXL;
using IronXL.Formatting;
using IronXL.Formatting.Enums;
using IronXL.Styles;

Imports IronXL
Imports IronXL.Formatting
Imports IronXL.Formatting.Enums
Imports IronXL.Styles

VB C#

After that, add the following lines of code to your main function:

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

//Create a specific conditional formatting rule.
ConditionalFormattingRule rule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");
//Set different style options.
rule.FontFormatting.IsBold = true;
rule.FontFormatting.FontColor = "#123456";
rule.BorderFormatting.RightBorderColor = "#ffffff";
rule.BorderFormatting.RightBorderType = BorderType.Thick;
rule.PatternFormatting.BackgroundColor = "#54bdd9";
rule.PatternFormatting.FillPattern = FillPattern.Diamonds;
//Add formatting with the specified region.
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule);

ConditionalFormattingRule rule1 = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10");
rule1.FontFormatting.IsItalic = true;
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single;
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1);

workbook.SaveAs("ApplyConditionalFormatting.xlsx");

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

//Create a specific conditional formatting rule.
ConditionalFormattingRule rule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");
//Set different style options.
rule.FontFormatting.IsBold = true;
rule.FontFormatting.FontColor = "#123456";
rule.BorderFormatting.RightBorderColor = "#ffffff";
rule.BorderFormatting.RightBorderType = BorderType.Thick;
rule.PatternFormatting.BackgroundColor = "#54bdd9";
rule.PatternFormatting.FillPattern = FillPattern.Diamonds;
//Add formatting with the specified region.
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule);

ConditionalFormattingRule rule1 = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10");
rule1.FontFormatting.IsItalic = true;
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single;
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1);

workbook.SaveAs("ApplyConditionalFormatting.xlsx");

Dim workbook As WorkBook = WorkBook.Load("test.xlsx")
Dim sheet As WorkSheet = workbook.DefaultWorkSheet

'Create a specific conditional formatting rule.
Dim rule As ConditionalFormattingRule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8")
'Set different style options.
rule.FontFormatting.IsBold = True
rule.FontFormatting.FontColor = "#123456"
rule.BorderFormatting.RightBorderColor = "#ffffff"
rule.BorderFormatting.RightBorderType = BorderType.Thick
rule.PatternFormatting.BackgroundColor = "#54bdd9"
rule.PatternFormatting.FillPattern = FillPattern.Diamonds
'Add formatting with the specified region.
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule)

Dim rule1 As ConditionalFormattingRule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10")
rule1.FontFormatting.IsItalic = True
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1)

workbook.SaveAs("ApplyConditionalFormatting.xlsx")

VB C#

We can define conditional formatting rules using the CreateConditionalFormattingRule() function. It is very easy to edit the font styles and border styling using IronXL. You can format the rows by using the row function. And, in the parameter, you will give the row number and formatting you want to apply. You can select highlight every nth row. You can see more details in our code example about conditional formatting support.

IronXL is free for development. However, there will be a watermark in the development phase. You can try it for free in production. You can activate the IronXL free trial without any payment or card information. After that, you can purchase it. There are different price plans which you can choose according to your needs, visit this licensing page for more information.

< PREVIOUS
How to Move Rows in Excel

NEXT >
Reading CSV Files in C#: a Tutorial