How to Alternate Row Colors in Excel with Conditional Formatting

Like word, data stored in Excel has format. Sometimes, we need to change the format of the data. Spire.XLS, an easy but powerful Excel .NET component can meet your need both in data dealing and format settings.

In this article, we will introduce you how to alternate row colors with conditional formatting.

Step 1: Select the range that you want to format

CellRange dataRange = sheet.AllocatedRange;

Step 2: Set conditional formation

Call the method AddCondition to add a new conditional formatting instance format1.

  • FirstFormula : It determines which cells to format.
  • FormatType : Set conditional formatting type
  • BackColor : Set as LightSeaGreen.
  • format1 sets the backcolor of the even rows as LightSeaGreen.
ConditionalFormatWrapper format1 = dataRange.ConditionalFormats.AddCondition();
format1.FirstFormula = "=MOD(ROW(),2)=0";
format1.FormatType = ConditionalFormatType.Formula;
format1.BackColor = Color.LightSeaGreen;

format2 sets the backcolor of the odd rows as Yellow.

Full code:

static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            CellRange dataRange = sheet.AllocatedRange;

            ConditionalFormatWrapper format1 = dataRange.ConditionalFormats.AddCondition();
            format1.FirstFormula = "=MOD(ROW(),2)=0";
            format1.FormatType = ConditionalFormatType.Formula;
            format1.BackColor = Color.LightSeaGreen;

            ConditionalFormatWrapper format2 = dataRange.ConditionalFormats.AddCondition();
            format2.FirstFormula = "=MOD(ROW(),2)=1";
            format2.FormatType = ConditionalFormatType.Formula;
            format2.BackColor = Color.Yellow;

            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("result.xlsx");

        }

Screenshot:

How to Alternate Row Colors in Excel with Conditional Formatting