Solved

Add condition to cell in Excel

Posted on 2009-04-08
3
449 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

828 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