VBA for Excel 2007 tutorial-top percents format

Top percents conditional format

In this example, you will learn VBA code to conditionally format a range of cells form A1 to A20 with top percents of Top10 rule. For instance, you want to highlight the top 10 percents or top 3 percents in the specified range of cells.

VBA for Excel: VBA example code to conditionally format cells with Top percent of Top10 rule

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()
End Sub
Sub ConFormat()
    Dim cf As Top10
    Dim i As Integer
    'Fill cells with sample data from 1 to 20
    For i = 1 To 20
        Range("A" & i) = i
    'Select the range of cells to apply Top10 conditional format
    'Create a Top10 object
    Set cf = Selection.FormatConditions.AddTop10()
    'Top 10% in the cells. You can change the value to meet your requirements
    'Ex: to specify Top 3% you can write cf.Rank=3
    cf.Rank = 10 '10%
    cf.Percent = True 'By percent
    'Format 10% of top cells with a mix of red and blue  interior colors
    cf.Interior.Color = RGB(255, 0, 255)
End Sub


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.