Macro that will hide/unhide sheets based on tab color.

[Rixonomic]

I'm relatively new to Excel, and all I know about macros is how to make a button that does nothing when I press it : )

So I have a large workbook (46 sheets) and I want to be able to hide/unhide certain groups of sheets based on their tab color. I found a code here that supposedly will do this:

Sub HideUnhide()
application.ScreenUpdating = False
Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Tab.ThemeColor = xlThemeColorLight2 And ws.Tab.TintAndShade = -0.249977111117893 Then
            ws.Visible = (ws.Visible = False)
        End If
    Next ws
application.ScreenUpdating = True
End Sub

But I have two problems:

1. I don't know exactly where to put this code. I tried making an ActiveX Control Command Button and copy-pasting the code into Microsoft Visual Basic, but whether it worked I know not, because of my second problem...

2. I don't know how to determine the ThemeColor or the TintAndShade of the colors I'm using (primarily Yellow). I tried some Google searches, but I couldn't find what I'm looking for.

Now, if there is an easier/better/more efficient way to hide/unhide certain groups of sheets, I would be more than happy to use another method. This is just what I came up with in my extremely limited understanding of programming and how to use excel.

[AB33]

Rix,
There is nothing wrong with the code. I do not know what these colours are
If ws.Tab.ThemeColor = xlThemeColorLight2 And ws.Tab.TintAndShade = -0.249977111117893
So I have changed this line to test if the code works, it does. It has hidden if my worksheets have either red,or yellow tab. In your case each work sheet has to fulfil both conditions since you have (and), mine is Or. Whle in excel. Do Alt+F8- This will take you to Visual basic page -Go to insert and copy and paste the code

Sub HideUnhide1()
Application.ScreenUpdating = False
Dim ws As Worksheet
On Error Resume Next
    For Each ws In Worksheets
        If ws.Tab.ThemeColor = red Or ws.Tab.TintAndShade = yellow Then
            ws.Visible = (ws.Visible = False)
        End If
    Next ws
Application.ScreenUpdating = True
End Sub

[Rixonomic]

Okay, it took some fiddling around, but I finally got it to work. Thank you for your help AB33!

[AB33]

You are welcome!

[Rixonomic]

I have realized, upon closer examination, that this code is not functioning in exactly the fashion I initially believed. It would appear to be the case that this macro will hide/unhide tabs that are any Standard Color, any color in the top row of Theme Colors, and any tab that has no color. But if I color the tabs any color other than those, they are unaffected by the code. Any ideas?

[AB33]

If the other sheets do not fall on this condition, they would remain unaffected. The code only hids sheets if they fulfil that condition, otherwise business as usual.

If ws.Tab.ThemeColor = xlThemeColorLight2 And ws.Tab.TintAndShade = -0.249977111117893 Then
            ws.Visible = (ws.Visible = False)
        End If

[Rixonomic]

Okay, so basically what I'm trying to say is that I want a macro that will hide/unhide only yellow tabs. Then I want a second macro that will hide/unhide only red tabs. Then a third for only blue tabs, etc... But this one line of code...

If ws.Tab.ThemeColor = red Or ws.Tab.TintAndShade = yellow Then

...hides/unhides every standard colored tab no matter what color it is; red, blue, yellow, orange: it doesn't matter. Even when I change the colors in the code it still affects all standard colored tabs with one click. That's not what I want. Maybe I wasn't clear about that, and for that I apologize. Like I said, I'm new to Excel and this is all just a little over my head :/