none
C# Excel create alternate row colours in Excel from C# code RRS feed

  • Question

  • Hi, i have found this code to do the above but it doesn't work - anyone got an idea as to why not? (I am not a member of the forum from where it came from and it is a pretty old thread anyway.) I want to have alternate row colours in an Excel sheet, actually preferably starting from row 2 or 3 as my (SQL-derived) headers will be in row 1. Unfortunately there is almost nothing else I have found on the web to do this (in C#). 

    // ALTERNATE ROW COLOURS
    Excel.FormatCondition format = xlWS.Rows.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Excel.XlFormatConditionOperator.xlEqual, "=MOD(ROW(),2)=0");
    format.Interior.Color = Excel.XlRgbColor.rgbBlue;

    • Moved by CoolDadTx Wednesday, June 10, 2015 3:56 PM Office related
    Wednesday, June 10, 2015 2:18 PM

Answers

  • Hi Lan,

    This rather simple (in the end) code solves the whole problem of alternate colours for rows. it also numbers the first column as well (starting from a certain row number depending on input). The only slight disadvantage currently is that the gridlines disappear of course and I want the same effect as gridlines in the coloured rows, but that is another question.

    int RowNumber = 1;
    Excel.Range RowFarben = null;
    // Wir schreiben nur Nummern rein - kein Gebrauch für Formular:
    for (int iRows = iRow; iRows <= iLastRow; iRows++) // Alle Spalten
    {
       xlWS.Cells[iRows, 1] = RowNumber.ToString(); ; // Start at 1 in later row.
       RowNumber++;
       // Farben lassen:
       if ((RowNumber % 2) == 0)
       {
             RowFarben = xlWS.Range[xlWS.Cells[iRows, 1], xlWS.Cells[iRows, iLastColumn]];
             RowFarben.Interior.Color = Excel.XlRgbColor.rgbLightBlue;
        }
    }


    • Marked as answer by Sansevieria Friday, June 12, 2015 2:37 PM
    • Edited by Sansevieria Thursday, June 18, 2015 3:37 PM
    Friday, June 12, 2015 2:36 PM

All replies

  • >> C# Excel create alternate row colours in Excel from C# code

    You may add a format for the rows by Range.FormatConditions.Add Method , I have made a demo that refered to the code you provided, and it works.

    >> actually preferably starting from row 2 or 3

    Since you want to start form the second row or third> row, you may make a Range from the this row, and then format the rows.

    The following code will format the color of the Excel rows.It will open an Excel file in the disk and format the Excel rows.When you run it, will it work as expected.

    //Choose a range from the Second row
            private void button2_Click(object sender, EventArgs e)
            {
                Excel.Application excel = new Excel.Application();
                excel.Visible = true; 
                string path = "D:\\test.xlsx";
                Excel.Workbook wb = excel.Workbooks.Open(path);
                Excel.Worksheet sheet = wb.Worksheets["Sheet1"];  
                Excel.Range r = sheet.Range["A2:M50"];
                Excel.FormatCondition format = r.Rows.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Excel.XlFormatConditionOperator.xlEqual, "=MOD(ROW(),2) = 0");
                format.Interior.Color = Excel.XlRgbColor.rgbGreenYellow;
            }

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 11, 2015 5:03 AM
    Moderator
  • Hi Lan, thanks for your reply but I still get the same failure/exception. Here is my code amended from yours: (I get an Exception seemingly on the macro formula)

    Excel.Range RowFarben = xlWS.Range[xlWS.Cells[iRow, 1], xlWS.Cells[iLastRow, iLastColumn]];            
    Excel.FormatCondition format = RowFarben.Rows.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Excel.XlFormatConditionOperator.xlEqual, "=MOD(ROW(),2) = 0");
    format.Interior.Color = Excel.XlRgbColor.rgbLightGoldenrodYellow; 


    • Edited by Sansevieria Thursday, June 11, 2015 1:16 PM
    Thursday, June 11, 2015 10:34 AM
  • Hi Lan,

    This rather simple (in the end) code solves the whole problem of alternate colours for rows. it also numbers the first column as well (starting from a certain row number depending on input). The only slight disadvantage currently is that the gridlines disappear of course and I want the same effect as gridlines in the coloured rows, but that is another question.

    int RowNumber = 1;
    Excel.Range RowFarben = null;
    // Wir schreiben nur Nummern rein - kein Gebrauch für Formular:
    for (int iRows = iRow; iRows <= iLastRow; iRows++) // Alle Spalten
    {
       xlWS.Cells[iRows, 1] = RowNumber.ToString(); ; // Start at 1 in later row.
       RowNumber++;
       // Farben lassen:
       if ((RowNumber % 2) == 0)
       {
             RowFarben = xlWS.Range[xlWS.Cells[iRows, 1], xlWS.Cells[iRows, iLastColumn]];
             RowFarben.Interior.Color = Excel.XlRgbColor.rgbLightBlue;
        }
    }


    • Marked as answer by Sansevieria Friday, June 12, 2015 2:37 PM
    • Edited by Sansevieria Thursday, June 18, 2015 3:37 PM
    Friday, June 12, 2015 2:36 PM
  • What version of Excel are you using?
    Friday, June 12, 2015 3:03 PM
  • Hi Bernie - Excel 2013
    Monday, June 15, 2015 8:14 AM