conditional format while update Excel Workbook using vb.net

Question

I have a Problem while updating my Excel Workbook with vb.net.

Basically I will create a timetable in Excel. So at my first line in Excel I have on each cell a date.

This is my Excel

For Cells C2 to I2 I have a conditional format with a formula:

=AND(C$1>=$A2;C$1<=$B2)

This works great. When I change the value of B2 using vb.net Microsoft.Office.Interop.Excel

    Dim ExcelFolder As String
    Dim selectedfile As String
    Dim excel As Application
    Dim workbook As Workbook
    Dim sheet As Worksheet

    ExcelFolder = "C:\temp"

    selectedfile = ExcelFolder & "\" & "test.xlsx"
    excel = New Application
    excel.Workbooks.Open(selectedfile)
    workbook = excel.ActiveWorkbook
    sheet = workbook.Worksheets(2)

    'change value on B2
    sheet.Cells(2, 2) = "07.10.2017"

    workbook.SaveAs("test.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, False, False, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
    workbook.Close()
    excel.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel) : excel = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook) : workbook = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet) : sheet = Nothing

It will change the value, but the background wouldn't change. I have to go manually to cell B2 and press ENTER again, after that the conditional format will work. I have tried to write a Date to the cell or change the cell format before and after inserting to Date, without success. I also tried to add a conditional format with vb.net after inserting the new data, without any success. Have you any ideas?

Answer 1

Ensure that the EnableCalculation property on your Worksheet object is set to true:

sheet.EnableCalcuation = True
sheet.Cells(2, 2) = "07.10.2017"