Clever Geek Handbook

Highlight every second visible line

I am trying to clear up every other visible line after I run hide lines containing a total of 0.

I have some code that makes some striped lines, but it doesn't seem like they are constantly showing on every other visible line.

Depending on the total number of stripes on the pin, there will be an almost smudge stain and sometimes it will look like an attached image.

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

thank

+3


source to share


2 answers


After some difficulty, I think I understand. You want to change the interior color of the visible lines, but what you are actually doing is based on a property .row

that does not depend on the visible / hidden lines. So your result is that the even lines are colored in RGB(228, 223, 235)

no matter which lines are hidden.

Without much involvement in the rest of your program, 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 lines shown as follows:

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
+1


source


You can do it without code, Conditional formatting will suffice

  • Select lines 1 through 2 (start in A1 for selection)
  • Add this conditional format test =MOD(SUBTOTAL(103,A1:$A$1),2)=0



enter image description here

  • The formula is SUBTOTAL

    used to count fuzzy records, it ignores hidden cells.
  • The section MOD

    highlights every other visible line.
+1


source






More articles:


All Articles