Highlight bottom 3 values

This tutorial shows how to highlight the bottom 3 numbers in a range using Excel and VBA

EXCEL METHOD 1. Highlight bottom 3 values

EXCEL

Select range > Home tab > Style group > Conditional Formatting > New Rules > Format only top or bottom ranked values > Select Bottom > Enter 3 > Format > Fill tab > Select color > OK > OK
1. Select the range in which you want to highlight the bottom 3 numbers.
Note: In this example we are selecting a range of cells from B3 to B9. 		Select range of cells - Highlight highest value
2. Select the Home tab. Select Home tab - Excel 2016
3. Click on Conditional Formatting in the Style group.
4. Click on New Rules. 		Select Conditional Formatting in Style group and click New Rules
5. Select Format only top or bottom ranked values.
6. Select the Bottom option from the drop down menu.
7. Enter a value of 3 in the input box menu.
Note: By default this value in 10.
8. Click on the Format button. 		Select options to highlight bottom 3 number
9. Click on the Fill tab and select the color that you want to highlight the cells that contain the lowest 3 value.
10. Click on OK. 		Select the Fill tab, select color and click OK
11. Click OK in the New Formatting Rule dialog box. Click OK in New Formatting Rule - bottom 3
12. This image show the result of these steps
Note: In this example the bottom 3 numbers in the selected range (B3:B9) are 100, 125 and 250 which are captured in cells B8, B4 and B9, respectively, and now highlighted in the color that we selected. 		Highlighted bottom 3

VBA METHOD 1. Highlight bottom 3 values using VBA

VBA

Sub Highlight_bottom_3_values()
'declare variables

Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range

Set ws = Worksheets("Sheet1")
Set ColorRng = ws.Range("B3:B9")
Bottomn = 3
'highlight the cells with bottom 3 numbers
For x = 1 To Bottomn
For Each ColorCell In ColorRng

If ColorCell.Value = Application.WorksheetFunction.Small(ColorRng, x) Then
ColorCell.Interior.Color = RGB(220, 230, 248)
End If

Next
Next x

End Sub

NOTES
Note 1: This VBA code will highlight the cells that contains the 3 smallest numbers in range B3 to B9 of "Sheet1".

RELATED TOPICS
Related Topic Description Related Topic and Description
Highlight cells with unique values How to highlight cells that contain unique values in a selected range
Highlight highest value How to highlight the highest number in a range
Highlight lowest value How to highlight the lowest number in a range
Highlight top 3 values How to highlight the top 3 numbers in a range