CODEWITHSUNDEEP
excelvba
excelguy
excelguy

Reputation: 1624

VBA, Find MIN value, Highlight row based on this value

I have a range of values, I want to find the MIN, then highlight the row of this Min value.

Sub worstcase()


Set Rng = .Range("H44:H54")
worstcase = Application.WorksheetFunction.Min(Rng)
Debug.Print worstcase

How can I highlight rows based on variable worstcase? I have my static range, and find the min value, but now I need to highlight the row of the worstcase variable.

Upvotes: 1

Views: 1524

Answers (3)

user10862412
user10862412

Reputation:

Create a conditional formatting rule based on the following formula.

=$H44=min($H$44:$H$54)

This VBA will create a CFR for rows 44:54.

With worksheets("sheet1").range("44:54")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$H44=min($H$44:$H$54)"
    .FormatConditions(.FormatConditions.Count).Interior.Color = vbred
End With

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54807

Highlight Row With Found Criteria

The code is highlighting each row where the minimum was found. Use Exit For to highlight only the first found.

The Code

Sub worstcase()

    Dim worstcase As Double ' Long for whole numbers.
    Dim rng As Range
    Dim cell As Range

    With Worksheets("Sheet1")
        Set rng = .Range("H44:H54")
        worstcase = Application.WorksheetFunction.Min(rng)
        Debug.Print worstcase

        For Each cell In rng
            If cell.Value = worstcase Then
                cell.EntireRow.Interior.ColorIndex = 3 ' Hightlight whole row.
                'cell.Interior.ColorIndex = 5 ' Hightlight only cell.
                'Exit For ' To highlight only the first found row.
            End If
        Next

    End With

End Sub

EDIT:

Sub worstcase()

    Const cFirst As Variant = "H"
    Const cLast As Variant = "Q"

    Dim worstcase As Double ' Long for whole numbers.
    Dim rng As Range
    Dim cell As Range

    With Worksheets("Sheet1")
        Set rng = .Range("H44:H54")
        worstcase = Application.WorksheetFunction.Min(rng)
        Debug.Print worstcase

        For Each cell In rng
            If cell.Value = worstcase Then
                .Range(.Cells(cell.Row, cFirst), .Cells(cell.Row, cLast)) _
                    .Interior.ColorIndex = 3 ' Hightlight cells.
                'Exit For ' To highlight only the first found cells.
            End If
        Next

    End With

End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

You could do it thus.

Won't work though if you have a repeated minimum.

Also you could use conditional formatting and avoid VBA.

Sub worstcase()

Dim Rng As Range, worstcase, i As Long

Set Rng = Range("H44:H54")

With Rng
    worstcase = Application.WorksheetFunction.Min(.Cells)
    i = Application.Match(worstcase, .Cells, 0)
    .Cells(i).EntireRow.Interior.Color = vbRed
End With

End Sub

Upvotes: 1

Related Questions