Highlight Bottom N Values with Excel VBA

Goal:

We want to write a VBA macro that will highlight the bottom N values in a specific range of cells in Excel.

Steps to Approach:

1. Input Range: We need to define the range where the values are located. This can be any selection of cells in your worksheet.
2. Bottom N Values: We will determine the bottom N values within that range.
3. Highlighting: Once we identify the bottom N values, we will highlight those cells using a color.

Detailed Code:

Sub HighlightBottomNValues()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim n As Integer
    Dim valuesArray() As Double
    Dim sortedArray() As Double
    Dim i As Integer
    Dim color As Long   
    ' Set the worksheet and the range (you can adjust these as needed)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1:A20") ' Change this to your desired range    
    ' Get the number of bottom values to highlight
    n = InputBox("Enter the number of Bottom N values to highlight", "Bottom N Values", 3)    
    ' Get the range of values and store them in an array
    ReDim valuesArray(1 To rng.Cells.Count)
    i = 1
    For Each cell In rng
        valuesArray(i) = cell.Value
        i = i + 1
    Next cell   
    ' Sort the values array to identify the bottom N values
    sortedArray = valuesArray
    Call SortArray(sortedArray)    
    ' Choose the highlight color (change as needed)
    color = RGB(255, 255, 0) ' Yellow highlight color   
    ' Loop through the range and highlight the bottom N values
    i = 1
    For Each cell In rng
        If IsInBottomN(cell.Value, sortedArray, n) Then
            cell.Interior.Color = color
        Else
            cell.Interior.ColorIndex = xlNone ' Remove any existing highlighting
        End If
    Next cell
End Sub

' Function to sort an array in ascending order
Sub SortArray(ByRef arr() As Double)
    Dim i As Long, j As Long
    Dim temp As Double   
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i) > arr(j) Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub
' Function to check if a value is among the bottom N values
Function IsInBottomN(value As Double, sortedArray() As Double, N As Integer) As Boolean
    Dim i As Integer
    IsInBottomN = False
    For i = 1 To N
        If value = sortedArray(i) Then
            IsInBottomN = True
            Exit Function
        End If
    Next i
End Function

Explanation of Code:

1. Set the Range and Worksheet:

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("A1:A20")

Here, we define the worksheet (Sheet1) and the range (A1:A20) where we want to perform the operation. You can change the worksheet name and range as per your requirement.

2. Input Box for Bottom N Values:

n = InputBox("Enter the number of Bottom N values to highlight", "Bottom N Values", 3)

This line prompts the user to input the number of « bottom N » values to highlight. The InputBox function allows the user to specify how many values they want to highlight.

3. Extract Values from the Range:

ReDim valuesArray(1 To rng.Cells.Count)
i = 1
For Each cell In rng
    valuesArray(i) = cell.Value
    i = i + 1
Next cell

Here, we loop through each cell in the selected range (rng) and store its value into an array called valuesArray. This array will be used to determine the bottom N values.

4. Sorting the Array:

sortedArray = valuesArray
Call SortArray(sortedArray)

The SortArray subroutine is used to sort the array in ascending order, so the bottom N values are easily identifiable at the beginning of the sorted array.

5. SortArray Subroutine:

Sub SortArray(ByRef arr() As Double)
    Dim i As Long, j As Long
    Dim temp As Double
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i) > arr(j) Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub

This subroutine implements a basic bubble sort algorithm to sort the array in ascending order. Sorting helps us easily identify the smallest N values (the « bottom N values »).

6. Identifying and Highlighting Bottom N Values:

For Each cell In rng
    If IsInBottomN(cell.Value, sortedArray, n) Then
        cell.Interior.Color = color
    Else
        cell.Interior.ColorIndex = xlNone ' Remove any existing highlighting
    End If
Next cell

This loop iterates through each cell in the range and checks if the cell’s value is in the bottom N values using the IsInBottomN function. If true, the cell is highlighted with the specified color (RGB(255, 255, 0) for yellow in this case). Otherwise, the highlight is removed.

7. IsInBottomN Function:

Function IsInBottomN(value As Double, sortedArray() As Double, N As Integer) As Boolean
    Dim i As Integer
    IsInBottomN = False
    For i = 1 To N
        If value = sortedArray(i) Then
            IsInBottomN = True
            Exit Function
        End If
    Next i
End Function

This function checks if the current cell’s value is among the bottom N values in the sorted array. If it finds the value, it returns True, otherwise, it returns False.

Notes:

• Highlight Color: The highlight color is set as yellow (RGB(255, 255, 0)), but you can change this to any other color using the RGB function.
• Handling Ties: The code does not handle ties in values. For example, if two cells have the same value and are among the bottom N values, only the first occurrence will be highlighted. Handling ties would require additional logic.
• Sorting Efficiency: For large ranges, a more efficient sorting algorithm than bubble sort (such as QuickSort) could be used for better performance.

Conclusion:

This VBA script provides a straightforward way to highlight the bottom N values in a range. By sorting the data and comparing the values, the macro identifies and highlights the cells with the smallest values. You can customize the worksheet, range, and color as needed.