excel - 循环过滤列表并突出显示总可见行的 20%

Question

我有一个自动过滤的列表。

我的目标是突出显示总可见行的 20%。

我已经有一个代码，但是，我不确定如何从第一个可见行开始突出显示 20%。

下面的代码突出显示所有可见行:

Sub HighlightTwentyPercent()

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim rng As Range
Set sht = Worksheets("Input raw")
Set StartCell = Range("F2")
Dim cl As Range


LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

Set rng = sht.Range(StartCell, sht.Cells(LastRow, 6))

For Each cl In rng.SpecialCells(xlCellTypeVisible)

    cl.Interior.Color = RGB(255, 12, 29)

Next cl

End Sub

例子:

如果总可见行是 50，那么它将突出显示 20%。在这种情况下，它将突出显示第一个可见行的 10 行。

Answer 1

以下代码将计算等于可见行的 20% 的行数，然后在达到该数字后退出 For 循环:

Sub HighlightTwentyPercent()

Dim sht As Worksheet: Set sht = Worksheets("Input raw")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim cl As Range
Dim TwentyPc As Long, i As Long
Set StartCell = Range("F2")

LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

Set rng = sht.Range(StartCell, sht.Cells(LastRow, 6))
TwentyPc = Round(rng.Rows.SpecialCells(xlCellTypeVisible).Count * 0.2, 0)

For Each cl In rng.SpecialCells(xlCellTypeVisible)
    i = i + 1
    If i > TwentyPc Then Exit For
    cl.Interior.Color = RGB(255, 12, 29)
Next cl
End Sub