[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Add condition to cell in Excel

Posted on 2009-04-08
3
Medium Priority
?
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: 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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 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