CODEWITHSUNDEEP
vbaexcel
Matt Taylor
Matt Taylor

Reputation: 671

highlight every second visible row

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

Example

Thx

Upvotes: 3

Views: 853

Answers (2)

brettdj
brettdj

Reputation: 55672

You can do this without code, Conditional Formatting will suffice

  1. Select rows 1 to 2 (start in A1 for the select)
  2. Add this conditional format test =MOD(SUBTOTAL(103,A1:$A$1),2)=0

enter image description here

  • The SUBTOTAL formula is using to count not blank entries, it ignores hidden cells.
  • The MOD section highlights every second visible row.

Upvotes: 1

A.S.H
A.S.H

Reputation: 29332

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

Upvotes: 1

Related Questions