Home > OS >  Changing excel cell color using vb.net
Changing excel cell color using vb.net

Time:09-16

hello guys i asked this question before but i just don't seem to find an answer can someone please tell me what i'm doing wrong in my code because even while i tried every methode on the internet no change is ever made on my excel file this my code for changing the color of a cell on an existing excel file

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlApp As New Microsoft.Office.Interop.Excel.Application
        ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem)
        Dim rangeToFormat As Excel.Range
        rangeToFormat = ws1.Cells(6, 5)
        rangeToFormat.Interior.Color = ColorTranslator.ToOle(Color.Red)

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim xlworkbook As Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlApp As New Microsoft.Office.Interop.Excel.Application
        OpenFileDialog1.ShowDialog()
        TextBox1.Text = OpenFileDialog1.FileName

        xlworkbook = xlApp.Workbooks.Open(TextBox1.Text, 0, True)

        For Each xlWorkSheet In xlApp.Sheets
            ComboBox1.Items.Add(xlWorkSheet.Name)
        Next
    End Sub

my code asks the user to choose an excel file, choose the worksheet that wants to work on and then click on a button to change the color of the cell(6,5) and other cells that i didn't add here to make my code easier to undrestand. thank you sooo much guys on advanced.

CodePudding user response:

Try this:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
    Dim xlApp As New Microsoft.Office.Interop.Excel.Application
    ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem)
    Dim rangeToFormat As Excel.Range
    rangeToFormat = ws1.Cells(6, 5)
    rangeToFormat.Font.Color = RGB(255, 20, 20)

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim xlworkbook As Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim xlApp As New Microsoft.Office.Interop.Excel.Application
    OpenFileDialog1.ShowDialog()
    TextBox1.Text = OpenFileDialog1.FileName

    xlworkbook = xlApp.Workbooks.Open(TextBox1.Text, 0, True)

    For Each xlWorkSheet In xlApp.Sheets
        ComboBox1.Items.Add(xlWorkSheet.Name)
    Next
End Sub

CodePudding user response:

for the one that are still looking for the answer i actually did find it you should just add after the color command:

        xlWb.Save()
        xlApp.Quit()
        xlApp = Nothing
  • Related