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.
  • 1592

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

Step 1: Start Microsoft Excel 2010

01.jpg

02.jpg
 

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

macroscreen.jpg
 

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

vbscreen.jpg
 

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.
  rng.FormatConditions.Delete
  
  ' 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

Clipboard01.jpg
 

Step 7: You output will shows on Microsoft Excel 2010

topten.jpg

Summary
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.

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.