Excel VBA循环遍历所有工作表并更改工作表颜色

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.

我希望找到一个excel vba，它将循环遍历工作簿中的所有工作表，并按以下顺序更改工作表颜色。 3,5,6,12,3,5,6,12,3,5,6,12等重复该图案，直到它用完纸张。下面的代码可以在随机颜色上更改它们，但我需要上面的模式。

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!

任何帮助都会很棒！ TY！

3 个解决方案

#1

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

#2

Use an array and index through it

通过它使用数组和索引

Sub sbColorAllSheetTab()
    Dim iCntr As Long, sht As Worksheet, arr
    arr = Array(3, 5, 6, 12, ...) ' any sequence

    For Each sht In ThisWorkbook.Worksheets
        sht.Tab.ColorIndex = arr(iCntr)
        iCntr = (iCntr + 1) Mod (UBound(arr) + 1)
    Next
End Sub

#3

Let me know what do you thing about this.

让我知道你对此有何看法。

Sub sbColorAllSheetTab()
Dim shtMod As Byte
'looping throgh the all the sheets of the workbook
For Each sht In ThisWorkbook.Worksheets
    shtMod = sht.Index Mod 4
    Select Case shtMod
        Case 1
            sht.Tab.ColorIndex = 3
        Case 2
            sht.Tab.ColorIndex = 5
        Case 3
            sht.Tab.ColorIndex = 6
        Case 0
            sht.Tab.ColorIndex = 12
    End Select
    Next
End Sub

#1