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.
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.
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
Step 7: You output will shows on Microsoft Excel 2010
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.