?
Solved

Add condition to cell in Excel

Posted on 2009-04-08
3
Medium Priority
?
486 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

840 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