Excel VBA Macro

Finding Cell with Minimum/Maximum Value in Active Worksheet

Posted on the October 4th, 2008 under Excel VBA Function, Workbook and Worksheet by Poer @ Excel VBA/Macro

Let say we want to find position of cell containing the minimum/maximum value in current/active Excel worksheet, and then after we found the cell, we will change the cell format to make it stand out before other cells.

The logic is simple, we just need to use Excel MIN function to find the minimum/maximum value on the worksheet, and then using Excel FIND function we will find which cell contain that minimum/ maximum value.

Excel VBA macro implementation of the algorithm above will look like below, change code Application.Min(oRg) into Application.Max(oRg) to find the maximum value instead of minimum value.

Sub FindMinValue()

    Dim oRg As Range, iMin As Variant

    Set oRg = Cells
    'Finding the minimum value
    iMin = Application.Min(oRg)

    'Select cell containing the min value
    oRg.Find(What:=iMin, _
        After:=oRg.Range("A1"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False _
        ).Select

    'Change selected cell format
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    'Displaying min value info
    With Selection
        MsgBox "Min value : " & iMin & vbCrLf & _
        "Cell position " & vbCrLf & _
        "Row : " & .Row & vbCrLf & _
        "Column : " & .Column
    End With

End Sub

As an additional info, the code above will display the cell info, row and column position of the cell containing the minimum/maximum value.

Related Entries

• How To Find the Last Row That Contain Data in Excel?
• Select All Cells With Formula In It
• Move Cursor To One Cell Below Last Row With Data
• Auto Format Excel Cells with Error Value
• Get Position of Last Column Containing Data with Excel VBA

External Resources

• Microsoft Excel 2003/2007 Video Tutorials

  Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.

• 101 Secrets of Microsoft Excel

  Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: cell function, excel formula, excel macro, excel tutorial, excel vba formula, Excel VBA Function, learn excel, macro cell, maximum cell value, minimum cell value, vba cell, vba excel, vba macro