Highlight Top N Values with Excel VBA

Goal:

The goal of this task is to create a VBA script that highlights the top N values in a given range of cells. The user can specify the number « N » of top values they want to highlight. We’ll use conditional formatting to change the cell colors for the top N values in the selected range.

Steps:

1. Input Range: The user will specify the range of cells (e.g., a column or a row).
2. Top N: The user will specify the number of top values (N) they want to highlight.
3. Highlighting: We will apply a conditional formatting rule to highlight the top N values using a different color.

VBA Code:

Sub HighlightTopNValues()
    Dim rng As Range
    Dim topN As Integer
    Dim cell As Range
    Dim i As Integer
    Dim valuesArray() As Double
    Dim sortedValues As Collection
    Dim value As Variant    
    ' Prompt the user for the range and the number N (top values)
    On Error Resume Next
    Set rng = Application.InputBox("Select a range of cells to highlight the top N values.", Type:=8)
    On Error GoTo 0    
    If rng Is Nothing Then
        MsgBox "No range selected. Exiting subroutine.", vbExclamation
        Exit Sub
    End If    
    topN = InputBox("Enter the number of top values to highlight (N):", "Top N Values", 5)    
    If topN <= 0 Or topN > rng.Cells.Count Then
        MsgBox "Please enter a valid number of top values (N).", vbCritical
        Exit Sub
    End If    
    ' Store the values from the range into 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 in descending order and store them in a collection
    Set sortedValues = New Collection
    For i = LBound(valuesArray) To UBound(valuesArray)
        If sortedValues.Count = 0 Then
            sortedValues.Add valuesArray(i)
        Else
            For j = 1 To sortedValues.Count
                If valuesArray(i) > sortedValues(j) Then
                    sortedValues.Add valuesArray(i), Before:=j
                    Exit For
                End If
            Next j
        End If
    Next i   
    ' Now apply the highlighting to the top N values
    i = 1
    For Each cell In rng
        ' Compare each cell's value with the sorted values
        If i <= topN Then
            If cell.Value = sortedValues(i) Then
                cell.Interior.Color = RGB(255, 223, 186) ' Highlight with a color (Light Orange)
            End If
        Else
            cell.Interior.ColorIndex = -4142 ' Remove any previous highlighting
        End If
        i = i + 1
    Next cell   
    MsgBox "Top " & topN & " values have been highlighted!", vbInformation
End Sub

Code Explanation:

1. Prompt for Range:
   • We start by using Application.InputBox to prompt the user to select a range of cells. The Type:=8 argument ensures the input will be a range.
   • If the user cancels the input, the code exits the subroutine.
2. Prompt for Top N Values:
   • The code uses an InputBox to prompt the user for the number of top values (topN) to highlight. It ensures that the entered value is a valid integer greater than 0 and less than or equal to the number of cells in the range.
3. Store Values in Array:
   • The values of the range are transferred into an array valuesArray to facilitate sorting.
4. Sorting the Values:
   • A Collection is used to sort the values in descending order. This ensures that we have a sorted list of the top N values.
5. Highlighting the Top N Values:
   • The sorted values are compared with each cell in the selected range.
   • If the value of the cell matches one of the top N values (based on sorting), it gets highlighted with a light orange color (RGB(255, 223, 186)).
   • Cells that are not in the top N are cleared of any previous formatting (cell.Interior.ColorIndex = -4142).
6. Completion Message:
   • Once the highlighting is applied, a message box will inform the user that the top N values have been highlighted.

Customization:

• Highlight Color: You can change the color of the highlighted cells by modifying the RGB(255, 223, 186) values in cell.Interior.Color. This defines the color using the RGB (Red, Green, Blue) model.
• Sorting Method: The sorting method used here is a manual insertion-based sorting technique using a Collection. For larger datasets, you could replace this with a more efficient sorting method, like using an array and QuickSort or BubbleSort.

Example Use Case:

• If you have a range of numbers in cells A1:A10, and you enter 3 as the « Top N » value, the three highest values in the range will be highlighted with the specified color.

Conclusion:

This VBA code provides a flexible way to highlight the top N values in any given range. It allows the user to define both the range and the number of values to highlight. The user interface via InputBox ensures the solution is dynamic and adaptable to various use cases.