highlight every second visible row

Question

I am trying to pinstripe every other visible row after i run a hide rows containing a total of 0.

I have some code that does some striping but doesn't seem to be constantly every other visible row.

Depending on the total qty the pin striping will be spot almost spot on and sometimes it will look like the attached picture.

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

After some difficulty I think I got it. You want to alternate the interior color of your visible rows but what you are actually doing is based on the .row property, which is independent on the visible/hidden rows. So your result is that even-numbered rows are colored with RGB(228, 223, 235) regardless of what rows are hidden.

Without much involvement in the rest of your routine, these lines should be fixed:

>     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

As a simple fix, try changing the above lines into the following:

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