【教學】Excel 如何將連續出現幾次的數字標示出來？

excel 儲存格連續數字 標示

Step 1 在功能列「開發人員」中程式碼選擇「Visual Basic」

Step 2 貼上下方程式碼，並修改工作表名稱、RGB底色、儲存格範圍及自訂連續出現的次數即可

Sub HighlightConsecutiveOnes()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim rowCount As Long
    Dim colCount As Long
    Dim i As Long
    Dim count As Integer
    Dim targetColor As Long


    Set ws = ThisWorkbook.Worksheets("template") 'tamplate更換為工作表名稱

    ' 設置標記底色
    targetColor = RGB(255, 0, 0)

    ' 設置儲存格範圍
    Set rng = ws.Range("A2:BE23")

    rowCount = rng.Rows.count
    colCount = rng.Columns.count

    For i = 1 To rowCount
        count = 0

        For Each cell In rng.Rows(i).Cells
            If cell.Value = 1 Then
                count = count + 1
            Else
                count = 0
            End If

            ' 若連續出現6次
            If count = 6 Then
                cell.Offset(0, -5).Resize(1, 6).Interior.Color = targetColor
            End If
        Next cell
    Next i
End Sub