Private Sub CommandButton1_Click() Dim rng As Range, i As Integer, myRange As String Dim max, min As Single myRange = InputBox("Enter the range") Set rng = Range(myRange) max= rng.Cells(1).Value min = rng.Cells(1).Value For i = 2 To rng.Count If rng.Cells(i).Value > max Then max = rng.Cells(i).Value ElseIf rng.Cells(i).Value < min Then min = rng.Cells(i).Value End If Next i MsgBox "The maximum value is " & max & vbCrLf & "The minimum value is " & min End Sub

Explanation 　  The myRange = InputBox("Enter the range")  statement will pop-up an input box to key in the range in the form of A1:A10, and the statement set rng = Range(myRange) assign the range to the variable rng. The If rng.Cells(i).Value > max Then max = rng.Cells(i).Value   statement will replace the max variable with a new  value if a bigger number is encountered. Similarly the ElseIf rng.Cells(i).Value < min Then min = rng.Cells(i).Value statement will replace the min variable with a new  value if a smaller number is encountered.