Solved

Add condition to cell in Excel

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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