CODEWITHSUNDEEP
c#excelvsto
roa765
roa765

Reputation: 295

c# VSTO Loop through each row and set format based on another cell value

I have VSTO application that outputs rows into excel. I am trying to apply some formatting based on values in a cell. My requirement is if the column 'V' contains a value "No" in the cell, the entire row that has values in it needs to be of a background colour "light grey". Note: Column V is a drop down list of either yes or no in excel. So its not a normal text based cell. Any ideas on how to do this?

var last = uiWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);

var lastUsedRow = last.Row < 6 ? 6 : last.Row;
var lastUsedColumn = last.Column;

var xlrange = uiWorksheet.get_Range("V:V");

if (xlrange.Value2.ToString() == "No")
{
    ???.EntireRow.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGray;
}

Upvotes: 0

Views: 314

Answers (1)

EylM
EylM

Reputation: 6103

In order to look for a value in the column, you need to use Find and FindNext functions. After it, you need to iterate over the results and change the color.

Please note following from MSDN:

The FindNext method's search wraps back to the beginning of the search range after it has reached the end of the range. Your code must ensure that the search does not wrap around in an infinite loop.

var xlrange = uiWorksheet.get_Range("V:V");

var searchFor = "No";
Range firstResult = null;
var currentResult = xlrange.Find(What: searchFor,
    LookIn: XlFindLookIn.xlValues, LookAt: XlLookAt.xlWhole,
    SearchOrder: XlSearchOrder.xlByRows
);

while(currentResult != null)
{
    if (firstResult == null)
        firstResult = currentResult;

    else if (currentResult.get_Address(XlReferenceStyle.xlA1)
             == firstResult.get_Address(XlReferenceStyle.xlA1))
    {
        break;
    }

    currentResult.EntireRow.Interior.Color = XlRgbColor.rgbLightGray;
    currentResult = xlrange.FindNext(currentResult);
}

Upvotes: 1

Related Questions