vba - 突出显示每隔一个可见行

Question

在运行总共包含 0 的隐藏行后，我尝试对所有其他可见行进行细条纹。

我有一些代码可以进行一些 strip 化，但似乎并不总是每隔一个可见行。

根据总数量，引脚条纹几乎完全正确，有时看起来像附图。

Sub Format_635()
Application.ScreenUpdating = False

Dim sht5 As Worksheet
Set sht5 = ThisWorkbook.Worksheets("635 BOM")

Call Unprotect
sht5.Activate

Dim lastRow As Long, lastCol As Long
Dim rng As Range
Dim WholeRng As Range

With sht5
    Set rng = Cells

    'last row
    lastRow = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

    'last column
    lastCol = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

    Set WholeRng = Range(Cells(9, "A"), Cells(lastRow, lastCol))
    WholeRng.Select

    With WholeRng
        With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 255, 255)
        .TintAndShade = 0
        Range(Cells(9, "A"), Cells(lastRow, lastCol)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
        Range(Cells(9, "A"), Cells(lastRow, lastCol)).Borders(xlInsideVertical).LineStyle = xlContinuous
        Range(Cells(9, "A"), Cells(lastRow, lastCol)).Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range(Cells(9, "A"), Cells(lastRow, lastCol)).Borders(xlEdgeRight).LineStyle = xlContinuous
        End With
    End With

    With WholeRng
    For Each rng In WholeRng
    If WorksheetFunction.Ceiling(rng.Row - 2, 1) Mod 2 = 0 Then
    rng.Interior.Color = RGB(228, 223, 235)
    End If
    Next
    End With
End With

Call Protect
sht5.Activate
Call NoSelect

Set rng = Nothing
Set WholeRng = Nothing
Application.ScreenUpdating = True
End Sub

Thx

Answer 1

经过一些困难，我想我明白了。您想要替换可见行的内部颜色，但您实际上所做的是基于.row属性，该属性独立于可见/隐藏行。因此，您的结果是偶数行都用 RGB(228, 223, 235) 着色，无论隐藏哪些行。

在不过多参与其余日常工作的情况下，应该修复这些行:

>     With WholeRng
>         For Each rng In WholeRng
>             If WorksheetFunction.Ceiling(rng.Row - 2, 1) Mod 2 = 0 Then
>                 rng.Interior.Color = RGB(228, 223, 235)
>             End If
>         Next
>     End With

作为一个简单的修复，请尝试将上面的行更改为以下内容:

Dim b As Boolean
For Each rng In WholeRng.Rows
    If Not rng.Hidden Then
        If b Then rng.Interior.Color = RGB(228, 223, 235)
        b = Not b
    End If
Next