Q:

Excel VBA that loops through all sheets and changes the sheet color

I was hoping to find an excel vba that will loop through all sheets in a workbook and change the sheet color in the below order.
3, 5, 6, 12, 3, 5, 6, 12, 3, 5, 6, 12 ect repeating the pattern until it runs out of sheets.
The below code work to change them on a random color but I need the above pattern.
Sub sbColorAllSheetTab()
'Declaration
Dim iCntr, sht
'This will hold the colorIndex number
iCntr = 2
'looping throgh the all the sheets of the workbook
For Each sht In ThisWorkbook.Worksheets
iCntr = iCntr + 1
'Applying the colors to Sheet tabs
sht.Tab.ColorIndex = iCntr
Next
End Sub

Any help would be great! ty!

A:

Try this:
Sub sbColorAllSheetTab()

    Dim iCntr, sht, arrColors, numColors

    arrColors = Array(3, 5, 6, 12) '<< array of color indexes

    iCntr = 0
    numColors = UBound(arrColors) + 1 '<< how many colors?

    For Each sht In ThisWorkbook.Worksheets
        sht.Tab.ColorIndex = arrColors((iCntr Mod 4)) '<< use Mod to cycle color
        iCntr = iCntr + 1
    Next

End Sub

