CODEWITHSUNDEEP
vbaexcel
Gabe Carvajal
Gabe Carvajal

Reputation: 65

changing tab color based on tab name excel vba

I want to be able to change tab colors based on the name of the tab cell. I tried to have my version choose from a list of names to change the tab name:

Sub SheetTabColor()

        Dim mySheets As Worksheets
        Dim mySheet As Worksheet

        Set mySheets = Sheets(Array("Christine", "Marina", "Roberto", "Urszula", "Lois", "Matt", "Stephanie", "Sally", "Iryna", "Katherine", "Matthew", "Julio", "Lavinia"))

        For Each mySheet In mySheets
            mySheet.Tab.Color = RGB(0, 255, 255)
        Next


    End Sub

After running this script I get an error at the "Set mySheets=" line

Error Msg

Any feedback would be appreciated, I understand that i might not even be close.

Upvotes: 2

Views: 3423

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Try this it puts the name array as strings instead of sheets.

Sub SheetTabColor()

    Dim mySheets() As Variant
    Dim mySheet As Variant

    mySheets = Array("Christine", "Marina", "Roberto", "Urszula", "Lois", "Matt", "Stephanie", "Sally", "Iryna", "Katherine", "Matthew", "Julio", "Lavinia")

    For Each mySheet In mySheets
        ThisWorkbook.Worksheets(mySheet).Tab.Color = RGB(0, 255, 255)
    Next

End Sub

This code will fail if any of the names in the array are not found as a worksheet name.

The following will skip any not found:

Sub SheetTabColor()

    Dim mySheets() As Variant
    Dim mySheet As Variant

    mySheets = Array("Christine", "Marina", "Roberto", "Urszula", "Lois", "Matt", "Stephanie", "Sally", "Iryna", "Katherine", "Matthew", "Julio", "Lavinia")

    For Each mySheet In mySheets
        On Error Resume Next
            ThisWorkbook.Worksheets(mySheet).Tab.Color = RGB(0, 255, 255)
        On Error GoTo 0
    Next

End Sub

Upvotes: 3

cyboashu
cyboashu

Reputation: 10433

Sheets is not same as Worksheets.

You are trying to assign Sheets to Worksheets, hence the error.

Use same type and the code works.

Sub test()

    Dim mySheets As Sheets
    Dim mySheet As Worksheet

    Set mySheets = Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    For Each mySheet In mySheets
        mySheet.Tab.Color = RGB(0, 255, 255)
    Next

 End Sub

Upvotes: 4

Related Questions