Solved

Add condition to cell in Excel

Posted on 2009-04-08
3
432 Views
Last Modified: 2012-05-06
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 vb.net but i dont know how to set a condition.

thanks
0
Comment
Question by:arcross
  • 2
3 Comments
 
LVL 12

Expert Comment

by:omegaomega
ID: 24100647
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.

Cheers,
Randy

    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)

        oRange.Select()

        oRange.FormatConditions.Delete()
 

         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

0
 
LVL 8

Author Comment

by:arcross
ID: 24101776
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")
0
 
LVL 12

Accepted Solution

by:
omegaomega earned 500 total points
ID: 24103042
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.

Cheers,
Randy


    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")

        oRange.Select()

        oRange.FormatConditions.Delete()
 

        ' 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

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now