Excel.DisplayFormat Method in MS Excel to Apply Conditional Formatting to a Range of Data in VB.NET

In this article I am going to explain about how to apply conditional formatting to a range of data in Microsoft Excel 2010.
  • 3402


In this article I am going to explain about how to apply conditional formatting to a range of data in Microsoft Excel 2010. For this we use Excel.DisplayFormat Method. Using this method we can apply conditional formatting to a range of data.

Microsoft Office 2010 offer some powerful tools, using this tools you can create application. Using Microsoft Visual Basic for Applications (VBA) you can create your own application according to your need. These application can performer some specific task.

For creating application we can use

  • VBA host of Excel 2010
  • VBA host of PowerPoint 2010
  • VBA host of Word 2010

NOTE : OneNote 2010 is not a VBA host.

Code that we use in this application are given below

Sub TestDisplayFormat() 

    Range("A1").Value = -900
    Range("A2").Value = -700
    Dim rng As Range
    rng = Range("A1:A2")
' Set up the conditional formatting:
    rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=0")
    With rng.FormatConditions(1)
        With .Font
            .Bold = 
            .Italic = True
            .Color = vbRed
        With .Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = vbYellow
    End With

End Sub
Private Sub CompareRangeAndDisplayFormat(rng As Range)

    ' Show off the differences in properties that have been changed
    ' by conditional formatting:
    Debug.Print(rng.Address & "======")
    Debug.Print(" rng.Interior.Color: " & rng.Interior.Color)
    Debug.Print(" rng.DisplayFormat.Interior.Color: " & rng.DisplayFormat.Interior.Color)
    Debug.Print(" rng.Font.Color: " & rng.Font.Color)
    Debug.Print(" rng.DisplayFormat.Font.Color: " & rng.DisplayFormat.Font.Color)
    Debug.Print(" rng.Font.Italic: " & rng.Font.Italic)
    Debug.Print(" rng.DisplayFormat.Font.Italic: " & rng.DisplayFormat.Font.Italic)
    Debug.Print(" rng.Font.Color: " & rng.Font.Color)
    Debug.Print(" rng.DisplayFormat.Font.Color: " & rng.DisplayFormat.Font.Color)
    Debug.Print(" rng.Borders.LineStyle: " & rng.Borders.LineStyle)
    Debug.Print(" rng.DisplayFormat.Borders.LineStyle: " & rng.DisplayFormat.Borders.LineStyle)

End Sub

Step for creating Application

Step 1 : Start MS Excel 2010 :

p (6).jpg

p6 (1).jpg

Step 2 : Using Alt + F11 Key Start Visual Basic for Applications (VBA) Window :

p6 (2).jpg

Step 3 : Select on Sheet 1 :

p6 (3).jpg

Step 4 : Right Click on Sheet 1 and select View Code :

p6 (4).jpg

p6 (5).jpg

Step 5 : Write Code in Visual Basic for Applications (VBA) Window :



Step 6 : Run Application using F5 :

Step 7 : Macros window will open, Select Macros name and click on Run Button :


Step 8 : Output of Application :




More Articles

© 2018 DotNetHeaven. All rights reserved.