代码之家 › 专栏 › 技术社区 › Ashley Thomson

高亮显示数值连续3次相同的单元格

highlight vba excel

Ashley Thomson · 技术社区 · 7 年前

任何帮助都将不胜感激

3 回复 | 直到 7 年前

Slai 7 年前

条件格式有点复杂。您可以选择 H3 试试这个公式:

= OR( AND(H3=H1, H3=H2), AND(H3=H2, H3=H4), AND(H3=H4, H3=H5) )

= OR( AND(H1="P",H2="P",H3="P"), AND(H2="P",H3="P",H4="P"), AND(H3="P",H4="P",H5="P") )

Tim Williams 7 年前

只要您的数据开始位置上方有几行。。。

user3469413 7 年前

我没有经常测试的快速代码,但请尝试以下方法:

    Sub Find_ThreeInARow()
    Dim column1 As Range
    Dim x As Range
    Dim y As Range
    Dim z As Range

    Set column1 = Application.InputBox("Select Column to Evaluate", Type:=8)
    If column1.Columns.Count > 1 Then
        Do Until column1.Columns.Count = 1
            MsgBox "Please select only 1 column."
            Set column1 = Application.InputBox("Select Column to Evaluate", Type:=8)
        Loop
    End If

    If column1.Rows.Count = 65536 Then
        Set column1 = Range(column1.Cells(1), column1.Cells(ActiveSheet.UsedRange.Rows.Count))
    End If

    For Each x In column1
        'Exit when we reach an empty cell
        If IsEmpty(x.Value) Then
            Exit Sub
        End If

        Set y = x.Offset(1, 0)
        Set z = x.Offset(2, 0)

        'Compare this cell to the next two in the column
        If x = y And x = z Then
            x.Interior.Color = vbYellow
            y.Interior.Color = vbYellow
            z.Interior.Color = vbYellow
        End If
    Next x
    End Sub