Getting the colour of Excel cells using interop

Question

Hi,

I have a range of cells the I retrieve from excel and I want to know how to get the colour of the cells in a column (all the cells are different colours). So far I have:

Range range = sheet.get_Range( "A1", "D10" );

Which gets me the data I need into an object array but I want to be able to iterate through the rows and get the cell colours for the "A" column. Is this possible?

I know its possible to do:

Range range = sheet.get_Range( "A1", Missing.Value );
var colour = range.Interior.Color;

but I would rather not do this for each individual cell.

Thanks

Answer 1

Each Range has a Cells and Rows property which returns a Range. In addition, Cells returns a Range. Here are some examples.

    Dim oRange As Excel.Range = CType(Me.Application.ActiveSheet, Excel.Range).Range("A1", "D10")

    For Each oRowRange As Excel.Range In oRange.Rows
        For Each oCellRange As Excel.Range In oRowRange.Columns
            Debug.WriteLine(oCellRange.Interior.Color)
        Next
    Next

    For Each oRowRange As Excel.Range In oRange.Range("A1")
        Dim oCell As Excel.Range = CType(oRowRange.Cells(RowIndex:=1, ColumnIndex:=1), Excel.Range)
    Next

    For i As Integer = 1 To oRange.Rows.Count
        Dim oCell As Excel.Range = CType(oRange.Cells(RowIndex:=i, ColumnIndex:=1), Excel.Range)
    Next