Add condition to cell in Excel

Hi, im exporting data to an excel sheet.
What id like to do is to gice a condition to a cell.
Lets say if the cell is > or < than 100 then the background color changes.

Ive added formulas to cells and i formatting the sheet using but i dont know how to set a condition.

Who is Participating?
omegaomegaConnect With a Mentor DeveloperCommented:
Hello, arcross,

The code will be essentially the same.  (See the attached snippet.)

Note that I have changed the name of the application variable from "excel" to "oExcel" because I want to avoid confusion with Excel library names (and didn't want to rename the variable in the example).  Also, I have changed all excel types to "Object" and disabled "Option Strict" to allow this (something I'm not fond of doing).  Because of this, type casting is no longer required.

Also, I have changed the "GreaterThan" condition to "GreaterThanOrEqualTo" for consistency sake.


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Const xlCellValue As Integer = 1
        Const xlLess As Integer = 6
        Const xlGreaterEqual As Integer = 7
        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")
        Dim oWorkbook As Object = oExcel.Workbooks.Add
        Dim oWorksheet As Object = oWorkbook.Worksheets(1)
        Dim oRange As Object
        Dim oFormatCond2 As Object
        Dim oFormatCond3 As Object
        oExcel.Visible = True
        oRange = oWorksheet.Range("B2:B5")
        ' Type:=1 is xlCellValue, Operator:=6 is xlLess.
        oFormatCond2 = oRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="100")
        oFormatCond2.Interior.ColorIndex = 50
        ' Operator:=7 is xlGreaterEqual.
        oFormatCond3 = oRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="100")
        oFormatCond2.Interior.ColorIndex = 15
    End Sub

Open in new window

Hello, arcross,

The attached snippet shows a simple example.

btw, with Excel where I don't know the methods or properties to use, I often find it helpful to just start recording a macro and then perform the desired actions manually.  The macro is generally not exactly what I want, but it often reveals the methods or properties that are required.


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oExcel As New Excel.Application
        Dim oWorkbook As Excel.Workbook = oExcel.Workbooks.Add
        Dim oWorksheet As Excel.Worksheet = CType(oWorkbook.Worksheets(1), Excel.Worksheet)
        Dim oRange As Excel.Range
        Dim oFormatCond2 As Excel.FormatCondition
        Dim oFormatCond3 As Excel.FormatCondition
        oExcel.Visible = True
        oRange = CType(oWorksheet.Range("B2:B5" ), Excel.Range)
         oFormatCond2 = CType(oRange.FormatConditions.Add( _
            Type:=Excel.XlFormatConditionType.xlCellValue, _
            Operator:=Excel.XlFormatConditionOperator.xlLess, _
            Formula1:="100"), Excel.FormatCondition)
        CType(oFormatCond2.Interior, Excel.Interior).ColorIndex = 50
        oFormatCond3 = CType(oRange.FormatConditions.Add( _
            Type:=Excel.XlFormatConditionType.xlCellValue, _
            Operator:=Excel.XlFormatConditionOperator.xlGreater, _
            Formula1:="100"), Excel.FormatCondition)
        CType(oFormatCond2.Interior, Excel.Interior).ColorIndex = 15
    End Sub

Open in new window

arcrossAuthor Commented:
thanks for that.
But how can i use the formatting using just objects?

Im not referencing to the excel library to avoid conflict with versions.

im using
dim excel as object
Excel = CreateObject("Excel.Application")
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.