excel - VBA，查找MIN值，根据该值突出显示行

Question

我有一个值范围，我想找到 MIN，然后突出显示这个 Min 值的行。

Sub worstcase()


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

如何根据变量最坏情况突出显示行？
我有我的静态范围，并找到最小值，但现在我需要突出显示最坏情况变量的行。

Answer 1

使用找到的标准突出显示行

代码突出显示找到最小值的每一行。使用 Exit For 仅突出显示找到的第一个。

编码

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

编辑:

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