Property ThemeColor Type Mismatch

[jfgoodhew1]

Hi All,

So I'm new to VBA and started out with the For Dummies book. I've got Excel 2010 and I'm struggling with a loop to change tab colors to color 1, 2, 3, ... of a theme. The problem is almost answered here: Colors question [Archive] - Xtreme Visual Basic Talk

My problem is I looked in Excel Help under ThemeColor property, and it says it is a Read/Write Variant.
I dimension SheetColor As Variant.
I then declare it (set its value?) using a string and the value of SheetNum (integer).
Then TypeName(SheetColor) shows String.
Then I get Type Mismatch error and the code fails.

So my desired result looks like this:
Sheet1.Tab.ThemeColor = xlThemeAccent1
Sheet2.Tab.ThemeColor = xlThemeAccent2
...
Then finally activate Sheet1 again.

I have code I wrote, which I can insert a MsgBox "OK" statement anywhere up to line ThemeColor = SheetColor.
I guess that is the line that has the problem, because SheetColor has type String, but ThemeColor requires a Variant argument.
If I use SheetColor.Value, I get requires an object (Value is a Property so doesn't fit the bill).
I found CStr which can convert anything to a string, but I didn't find an equivalent to help with converting to Variant (should be CVar).

Private Sub TabColor()
Application.ScreenUpdating = False
    Dim SheetNum As Long
    Dim SheetColor As Variant
    Dim SheetName As String
    Dim Sht As Sheets
    
    For SheetNum = 1 To Sheets.Count Step 1
        Worksheets(SheetNum).Activate
        SheetColor = "xlThemeColorAccent" & SheetNum
            With ActiveWorkbook.ActiveSheet.Tab
                .ThemeColor = SheetColor
            End With
        SheetNum = SheetNum + 1
    Next SheetNum
    Sheet1.Activate
Application.ScreenUpdating = True
End Sub

Any help getting this to work?

 

[jfgoodhew1]

To clarify, my end goal is to cycle through the colors in a theme (hopefully a custom theme in the end but here I use a built in theme for simplicity) to put clients' branding colors into key positions in my spreadsheet, like the tab color.

Oh and now I know, manually incrementing SheetNum was wrong. I deleted that line. Next SheetNum does that for me.

I'm sorry bumping is really bad form, I'm just wondering if anyone is working on this, and what did I do wrong in my post to get no suggestions/replies? How can I do better next time?

I'm not trying to be unappreciative, I genuinely haven't managed to find an answer elsewhere or here, and would really really like someone with more knowledge than me to remember/figure it out and post back. This forum is crazy-busy so posts disappear down the list very quickly!

Best regards,
James

 

[jfgoodhew1]

Solved at XtremeVBA by MPi here: Colors question - Xtreme Visual Basic Talk ThemeColor is apparently waiting for a variable type Long. Why that's not in the help file I'll never know. So, xlThemeColorAccent1 must correspond through some background codey way into a number stored in a Long variable. Out.