Color every second row in a table

Question

I have got a table with some merged cells and I would like to color every second row, starting with the first, via a VBA code.

Thats how I tried it:

Sub test()
    Dim Zeile As Long
    With Tabelle2
        For Zeile = 1 To .UsedRange(Rows.Count).End(xlUp).Row Step 2
         .Range(.Cells(Zeile, 1),.Cells(Zeile,8)).Interior.ColorIndex= 15
        Next
    End With
End Sub

The table looks like this:

And should look like this:

Thank you in advance!

Answer 1

I believe you're looking for something like this.

We add a boolean flag so we can flip back and forth using it (cf).

We can Resize the MergeArea instead of the cell value itself.

If there are merged cells, it will take that area into account - if not, it won't.

Then, add the potential MergeArea cell count to our row counter (Zeile).

Sub ColorEveryOther()
Dim cf As Boolean
Dim Zeile As Long
Dim lr As Long
lr = ActiveSheet.UsedRange.Rows.CountLarge
For Zeile = 1 To lr
    If Not cf Then Range("A" & Zeile).MergeArea.Resize(, 8).Interior.ColorIndex = 15
    Zeile = (Zeile + Range("A" & Zeile).MergeArea.Cells.CountLarge) - 1
    cf = Not cf
Next Zeile
End Sub

Results:

EDIT:

This is your code updated.

I also cleaned up the previous code a bit.

Sub test()
    Dim Zeile As Long
    Dim cf As Boolean
    With Tabelle2
        For Zeile = 1 To .UsedRange(Rows.Count).End(xlUp).Row
            If cf = False Then .Cells(Zeile, 1).MergeArea.Resize(, 8).Interior.ColorIndex = 15
            Zeile = (Zeile + .Cells(Zeile, 1).MergeArea.Cells.CountLarge) - 1
            cf = Not cf
        Next
    End With
End Sub

Explanation from comments:

cf = Not cf is just a shortcut for:

If cf = True Then
    cf = False
Else
    cf = True
End If

Let's set cf = False and walk through it.

cf = Not False = True

cf = Not True = False

I hope that explanation is sufficient :)

The reason for doing this is we can't just MOD the Row because it changes by variable amounts potentially.

Answer 2

Dim Zeile As Double
Dim WhiteColor As Boolean
WhiteColor = False

Dim RangeSize As Byte

Range("A1").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Zeile = ActiveCell.Row

Range("A1").Select

Do Until ActiveCell.Row = Zeile + 1
    RangeSize = Selection.Count

    If WhiteColor = False Then
        Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row + RangeSize - 1, 8)).Interior.Color = RGB(191, 191, 191)
        WhiteColor = True
    Else
        Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row + RangeSize - 1, 8)).Interior.Color = vbWhite
        WhiteColor = False
    End If
    ActiveCell.Offset(1, 0).Select
Loop

I tried the code above and it worked for me: