如何写宏以突出显示筛选出的行 [英] How To Write Macro for highlight filtered rows alternatively

问题描述

如何在Excel中为这种形式的结构编写宏以交替突出显示已过滤的行..提前致谢

How to Write Macro for this structure of Formation in Excel To Highlight Filtered rows alternatively.. Thanks in Advance

推荐答案

如果将您的号码分为总是个重复的大块，那么您可以使用以下VBA代码:

if your numbers are divided into chunks of always different numbers repetitions, then you could use this VBA code:

Sub main()
    Dim item As Variant
    Dim startRow As Long
    Dim okHighlight As Boolean

    With Range("A1", Cells(Rows.count, 1).End(xlUp))
        For Each item In GetUniqueValues(.Cells).Items
            If okHighlight Then .Range(.Cells(startRow, 1), .Cells(item, 1)).Interior.ColorIndex = 48
            startRow = item + 1
            okHighlight = Not okHighlight
        Next
    End With
End Sub


Function GetUniqueValues(rng As Range) As Dictionary
    Dim cell As Range
    Dim dict As Dictionary

    Set dict = New Dictionary
    With dict
        For Each cell In rng
            .item(cell.Value) = cell.row - rng.Rows(1).row + 1
        Next
    End With
    Set GetUniqueValues = dict
End Function

---

可以使用帮助器列进行条件格式设置

---

a Conditional formatting approach is possible with a helper column

假设:

• 您的数据在A列中，从第2行开始

• your data are in column A, beginning from row 2

B列免费

然后:

• 在辅助列B的单元格中编写以下公式:

• write the following formula in helper column B cells:

= IF(A2<> A1，B1 + 1,0)

=IF(A2<>A1,B1+1,0)

使用以下公式将条件格式应用于A列:

apply conditional formatting to column A with the following formula:

= INT(B2/2)= B2/2

=INT(B2/2)=B2/2

并选择要突出显示单元格的格式

and choosing the format you like to highlight cells

这篇关于如何写宏以突出显示筛选出的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！