CODEWITHSUNDEEP
vbaexcel
perbrethil
perbrethil

Reputation: 131

VBA Excel change color of multiple tabs

I wanted to do something I thought was simple, but apparently it is not. I needed a script that changes the color of multiple tabs at the same time. If I use the macro recorder it kinda does its job but it makes a enormous block of code for every separate tab each, so i was hoping i could throw an array into it, but i can't seem to figure it out.

I came this far before i was stuck:

Sub TESTCOLOR2()
Dim ArrayOne As Variant
ArrayOne = Array("800", "1000", "1100", "1200", "1300", "1400", "1500", "1600")

    With ArrayOne.Tab
        .ThemeColor = 65535
        End With
    End Sub

I get an error "Object required" and that's about it..

Upvotes: 3

Views: 992

Answers (1)

Vityata
Vityata

Reputation: 43595

To make 3 sheets with the same color, passed by an array, the following is possible:

Public Sub MultipleTabChanger()

    Dim wsToBeColored As Variant
    wsToBeColored = Array("Sheets2", "Sheets4", "Sheets3")  'these are names
    Dim singleWs As Variant

    For Each singleWs In wsToBeColored
        Worksheets(singleWs).Tab.ThemeColor = 7
    Next

End Sub

You loop through the variants of the array and pass them as a parameter to Worksheets(parameter). The Tab.ThemeColor is changed for all of them.

To set different colors to different sheets, the small trick in arrays and collections (Worksheets is a collection), is that collections start with 1 and arrays with 0. Thus, we have the Worksheets(i + 1) to avoid the 0-th element:

Public Sub TestMe()

    Dim arrColors As Variant
    arrColors = Array(12, 2, 3, 4, 5)    'these are colors

    Dim i As Long
    For i = LBound(arrColors) To UBound(arrColors)
        Worksheets(i + 1).Tab.ThemeColor = arrColors(i)
    Next i

End Sub

You would get this:

enter image description here

The tab colors do not take every value for color. I get errors with values above 13. To get the correct value, use the macro-recorder.

Upvotes: 6

Related Questions