VBA for Excel 2007 tutorial-Data Bars conditional format


Data Bars conditional format

By formatting a range of cells with Data Bar rule, the higher values will have longer horizontal bars. In contrast,  the lower values will have shorter horizontal bars. For instance, you want to format the random values(from 1 to 100) in the range of cells from A1 to A10 in which the higher values will have long blue bars and the lower values will have shorter blue bars.


VBA for Excel: VBA example code to conditionally format the cells with Data Bars

To run this VBA example code, you can copy and paste the VBA code below in to the block of Worksheet_Activate() procedure. To open this block, double-click the current worksheet, then in the objects dropdown list select Sheet1 and in events list select Activate.



VBA code for this example:



Option Explicit
Private Sub Worksheet_Activate()
    ConFormat
End Sub
 
Sub ConFormat()
 
    Dim cf As Databar
    Dim fc As FormatColor
    Dim i As Integer
    'Fill cells with random sample data from 1 to 100
    For i = 1 To 10
        Randomize
        Range("A" & i) = Int(100 * Rnd()) + 1
    Next
   
    'Select the range of cells to apply data bars conditional format
    Range("A1:A10").Select
   
    'Create an DataBar object
    Set cf = Selection.FormatConditions.AddDatabar()
    cf.MinPoint.Modify xlConditionValueLowestValue, 1 'min value
    cf.MaxPoint.Modify xlConditionValueHighestValue, 100 'max value
    Set fc = cf.BarColor ' Create FormatColor object
    fc.Color = RGB(0, 0, 255) 'Set bar color
      
End Sub

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.