CODEWITHSUNDEEP
vbaexcel
Matt Taylor
Matt Taylor

Reputation: 671

How can I color every other row, but skip rows marked X?

How can I color every other row but skip any row where Column A = X?

Whats wrong is it colors over my sub heading rows. I am trying to get it to skip the heading rows which is marked by an invisible X in Column A.

Can it skip the sub headings and the row below the sub heading row be white? Kind of like its starting over again.

This is the code I have that colors rows white then gray to the end for the entire range:

Sub Format()
Application.ScreenUpdating = False

Dim sht2 As Worksheet
Set sht2 = ThisWorkbook.Worksheets("Email Form")

sht2.Activate
sht2.Unprotect

Dim LastRow As Long, LastCol As Long
Dim rng As Range
Dim WholeRng As Range

With sht2
    Set rng = Cells

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

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

    Set WholeRng = Range(Cells(4, "B"), Cells(LastRow, LastCol))
    WholeRng.Select

    With WholeRng
        With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 255, 255)
        .TintAndShade = 0
        Range(Cells(4, "B"), Cells(LastRow, LastCol)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
        Range(Cells(4, "B"), Cells(LastRow, LastCol)).Borders(xlInsideVertical).LineStyle = xlContinuous
        Range(Cells(4, "B"), Cells(LastRow, LastCol)).Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range(Cells(4, "B"), Cells(LastRow, LastCol)).Borders(xlEdgeRight).LineStyle = xlContinuous
        End With
    End With

    Dim b As Boolean
    For Each rng In WholeRng.Rows
        If Not rng.Hidden Then
            If b Then rng.Interior.Color = Black
            b = Not b
        End If
    Next
End With

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

Upvotes: 0

Views: 159

Answers (1)

David Rushton
David Rushton

Reputation: 5030

You could expand your current if statement, using the and operator.

Example:

Dim b As Boolean
For Each rng In WholeRng.Rows
    If Not rng.Hidden Then
        ' UPDATED LINE BELOW.
        If b And sht2.Cells(rng.Row, 1) <> "x" Then rng.Interior.Color = Black
        b = Not b
    End If
Next

The code extracts the current row number from the rng object. It uses that to peek at the contents of column a.

An alternative approach is to use Excel's built-in conditional formatting. This is probably the easier method.

Upvotes: 1

Related Questions