Saturday, 24 January 2015

VBA find min and max value then highlight them with specified color

Sub minmax_value()
Dim number1 As Integer
Dim number2 As Integer
Dim cell As Range
Dim rng As Range
Set rng = ActiveSheet.UsedRange

For Each cell In rng
On Error Resume Next
number1 = Application.WorksheetFunction.Max(ActiveSheet.UsedRange)
number2 = Application.WorksheetFunction.Min(ActiveSheet.UsedRange)

If cell.Value = number1 Then
cell.Interior.Color = vbGreen
ElseIf cell.Value = number2 Then
cell.Interior.Color = vbRed
End If
Next cell

MsgBox "Maximum Value : " & number1 & vbNewLine & vbNewLine & "Minimum Value : " & number2

End Sub


No comments:

Post a Comment