title: Retain formatting when exporting datagridview to excel
body: <p>I have a working code which imports Excel data to a datagridview and applies background color based on different limit values for each column. I want to retain the background color when I export this to Excel, but I cannot find out how to implement this in my code.</p>
  
  <p>First an example of how I apply background color to the datagridview:</p>
  
  <pre><code>        Dim Ul1Zn As Double = 200
      Dim Ul2Zn As Double = 500
      Dim Ul3Zn As Double = 1000
      Dim Ul4Zn As Double = 5000
      Dim Ul5Zn As Double = 25000
  
      For i As Double = 0 To Me.DataGridView2.Rows.Count - 1
          If Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &lt; Ul1Zn Then
              Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Style.BackColor = Color.DodgerBlue
          ElseIf Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &gt;= Ul1Zn And Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &lt; Ul2Zn Then
              Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Style.BackColor = Color.LawnGreen
          ElseIf Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &gt;= Ul2Zn And Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &lt; Ul3Zn Then
              Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Style.BackColor = Color.Yellow
          ElseIf Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &gt;= Ul3Zn And Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &lt; Ul4Zn Then
              Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Style.BackColor = Color.Orange
          ElseIf Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &gt;= Ul4Zn And Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &lt; Ul5Zn Then
              Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Style.BackColor = Color.Red
          ElseIf Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Value &gt;= Ul5Zn Then
              Me.DataGridView2.Rows(i).Cells("Zn (Sink)").Style.BackColor = Color.BlueViolet
          End If
      Next
  </code></pre>
  
  <p>(Edited) And this is my export code:</p>
  
  <pre><code>Private Sub ExportToExcel()
      ' Creating a Excel object.
      Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
      Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)
      Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
  
      Try
  
          worksheet = workbook.ActiveSheet
  
          worksheet.Name = "ExportedFromDatGrid"
  
          Dim cellRowIndex As Integer = 1
          Dim cellColumnIndex As Integer = 1
  
  
          'Loop through each row and read value from each column.
          For i As Integer = 0 To DataGridView2.Rows.Count - 2
              For j As Integer = 0 To DataGridView2.Columns.Count - 1
                  ' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                  If cellRowIndex = 1 Then
                      worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Columns(j).HeaderText
                  Else
                      worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Rows(i).Cells(j).Value
                  End If
                  cellColumnIndex += 1
              Next
              cellColumnIndex = 1
              cellRowIndex += 1
          Next
  
  
          'As
          Dim UL1As As Double = 8
          Dim UL2As As Double = 20
          Dim UL3As As Double = 50
          Dim Ul4As As Double = 600
          Dim Ul5As As Double = 1000
  
          'Gir ikke feilmelding, men lager heller ikke farge
  
  
          For i As Integer = 0 To DataGridView2.Rows.Count - 2
              For j As Integer = 0 To DataGridView2.Columns.Count - 1
                  If Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; UL1As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DodgerBlue)
                  ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= UL1As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; UL2As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LawnGreen)
                  ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= UL2As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; UL3As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
                  ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= UL3As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; Ul4As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange)
                  ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= Ul4As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; Ul5As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                  ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= Ul5As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet)
                  End If
                  cellColumnIndex += 1
              Next
              cellColumnIndex = 1
              cellRowIndex += 1
          Next
  
          'Getting the location and file name of the excel to save from user.
          Dim saveDialog As New SaveFileDialog()
          saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
          saveDialog.FilterIndex = 2
  
          If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
              workbook.SaveAs(saveDialog.FileName)
              MessageBox.Show("Export Successful")
          End If
      Catch ex As System.Exception
          MessageBox.Show(ex.Message)
      Finally
          excel.Quit()
          workbook = Nothing
          excel = Nothing
      End Try
  
  End Sub
  </code></pre>
  
  <p>Progress! I removed the argument about "if column name is As (Arsen)..." Now the entire row gets colored, however, and I only want the individual cells under the relevant column to be affected. My original column specifying argument did not work, apparently. Anyone guess what was wrong with it?</p>
  
  <p>Previous code. Did not color background, but did not crash:</p>
  
  <pre><code>For i As Integer = 0 To DataGridView2.Rows.Count - 2
              For j As Integer = 0 To DataGridView2.Columns.Count - 1
                  If Me.DataGridView2.CurrentCell.OwningColumn.Name = "As (Arsen)" And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; UL1As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DodgerBlue)
                  ElseIf Me.DataGridView2.CurrentCell.OwningColumn.Name = "As (Arsen)" And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= UL1As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; UL2As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LawnGreen)
                  ElseIf Me.DataGridView2.CurrentCell.OwningColumn.Name = "As (Arsen)" And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= UL2As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; UL3As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
                  ElseIf Me.DataGridView2.CurrentCell.OwningColumn.Name = "As (Arsen)" And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= UL3As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; Ul4As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange)
                  ElseIf Me.DataGridView2.CurrentCell.OwningColumn.Name = "As (Arsen)" And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= Ul4As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &lt; Ul5As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                  ElseIf Me.DataGridView2.CurrentCell.OwningColumn.Name = "As (Arsen)" And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value &gt;= Ul5As Then
                      worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet)
                  End If
                  cellColumnIndex += 1
              Next
              cellColumnIndex = 1
              cellRowIndex += 1
          Next
  </code></pre>