I am new to VB syntax, and im struggling with adding conditions to an excel export. I have a dataset (ds2) that populates a DataGridView (dgvBreaks).The data in the dataset will look something like this:

I want to export the data from the gridview or dataset to excel and format certain rows based on a cell value. FOR EXAMPLE: Highlight a row in red if the Actual hours is less than 8. so that this row would be in red:

Currently I export the data to an excel sheet using the code below:

    Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer
    xlApp = New Microsoft.Office.Interop.Excel.Application
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")
    For i = 0 To dgvDataBreaks.RowCount - 2
        For j = 0 To dgvDataBreaks.ColumnCount - 1
            For k As Integer = 1 To dgvDataBreaks.Columns.Count
                xlWorkSheet.Cells(1, k) = dgvDataBreaks.Columns(k - 1).HeaderText
                xlWorkSheet.Cells(i   2, j   1) = dgvDataBreaks(j, i).Value.ToString()
            Next
        Next
    Next
    Dim path As SaveFileDialog = New SaveFileDialog
    path.ShowDialog()
    If DialogResult.OK Then
        xlWorkSheet.SaveAs(path.FileName)
    End If
    xlWorkBook.Close()
    xlApp.Quit()
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)
    MsgBox("You can find the file at: "   path.FileName)

But I am not sure how to add the conditional check. (How would I check only the 'Actual Hours' for instace?)

CodePudding user response：

As JohnG stated in the comments, it is quite simple:

xlWorkSheet.Range(xlWorkSheet.Cells(i   2, 1), xlWorkSheet.Cells(i   2, 8)).Interior.Color = ColorTranslator.ToOle(Color.Red)