Display Top Ten Percent Item and bottom Ten percent Item using VBA

Here we will see how to display the top ten percent item and bottom Item percent item within the specified range.
  • 2369

Here we will see the steps to create the Application. The Steps are as follows:

Step 1: Start Microsoft Excel 2010



Step 2: Now press Alt + F11 to open Microsoft Visual Basic for Applications


Step 3: Now choose Sheet1 to write your code from the project window


Step 4: Write the following Code in code window

Sub DemoAddTop10()
  ' Fill a range with random numbers.
  ' Mark the top 10% of items in yellow, and the bottom
  ' 10% of the items in red.

  ' Set up a range, and fill it with random numbers.
  Dim rng As Range
  Set rng = Range("A1:F15")
  SetupRangeData rng
  ' Clear any existing format conditions.
  ' Set up a condition that formats the top
  ' 10 percent of items on yellow.
  Dim fc As Top10
  Set fc = rng.FormatConditions.AddTop10
  fc.Percent = True
  fc.TopBottom = xlTop10Top
  fc.Interior.Color = vbYellow
  ' Set up a condition that formats the bottom
  ' 10 percent of items in red.
  Set fc = rng.FormatConditions.AddTop10
  fc.TopBottom = xlTop10Bottom
  fc.Percent = True
  fc.Interior.Color = vbRed
End Sub
Sub SetupRangeData(rng As Range)
  rng.Formula = "=RANDBETWEEN(1, 100)"
End Sub

Step 5: Press F5 to run the application.

Step 6: Macros window will open, here check the macro name and hit the run button


Step 7: You output will shows on Microsoft Excel 2010


In the above output screen we can see easily that top 10 items are coming in Yellow color and bottom ten items are coming in red color.


More Articles

© 2020 DotNetHeaven. All rights reserved.