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

Question

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

Just a couple of comments - your LastRow, LastCol variable could hold nothing if the sheet is blank and the WholeRng will throw an error as a result. The WholeRng.Select line isn't needed - you'll colour the interior without it being selected. You might also want to resize your WholeRng to add borders rather than use the LastRow, LastCol on the ActiveSheet (you haven't qualified the sheet name with your range references). — Darren Bartrup-Cook
Do you need VBA for this? Just thinking: if you use conditional formatting, based on a formula, and you use as a formula condition : =Offset(here,1-Column,0) == "x" => this would look for every cell, go 1-Column to the right (or Column - 1 to the left, ending up in column A), and then check if the value equals "x". Just one problem: does anybody know how to fill in the first argument of Offset() formula? How to say : "Offset from this cell onwards"? — Dominique

David Rushton David Rushton · Accepted Answer · 2017-04-28T13:03:40

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.

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

1 Answers