Switching

Cycle through colors

Option Explicit

Sub switch_row()

    Dim colors() As Variant
    Dim current_color, i, new_index As Long
    
    ' Set up color array
    colors = Array(2, 3, 4, 6, 7, 8)
    
    ' Find current row color
    current_color = ActiveCell.EntireRow.Interior.ColorIndex

    ' Loop through the array and see if the current row color is in the array of allowed colors
    i = 0
    Do Until i > UBound(colors)
        If current_color = colors(i) Then
            If i = UBound(colors) Then      ' If the found color is the last value in the array, restart the array
                new_index = 0
            Else
                new_index = i + 1           ' Otherwise, increase the array index by 1
            End If
            Exit Do
        Else
            new_index = 0                   ' The color isn't in the array. Get it started on the color changing train!
        End If
        i = i + 1
    Loop
    
    ' Color the row with the new color
    ActiveCell.EntireRow.Interior.ColorIndex = colors(new_index)

End Sub

' This could have all been done with cases too
' Select Case ActiveCell.Interior.ColorIndex
' Case 2:   newColor = 3
' Case 3:   newColor = 4
' ...
' Case Else:    newColor = 2