Hide sheets if colored

[xcdave]

I have a workbook with many sheets - some are colored, some are not.

I would like to create a macro that hides all sheets that are colored, leaving only sheets with no color.

Can anyone help?

Thanks!

 

[Asala42]

Do you mean the Tab Color? Something like this might work for what you're going for:

Sub HideTabs()

Dim i As Integer

For i = 1 To Sheets.Count
    If Sheets(i).Tab.ColorIndex <> -4142 Then
        Sheets(i).Visible = False
    Else
        Sheets(i).Visible = True
    End If
Next i

End Sub

 

[xcdave]

That works great!

I'm curious - how did you find out that the color index was -4142?

Dave

 

[Greg Truby]

Object explorer in the VBE. Look up the color constants, specifically, xlColorIndexNone

^edit Well, that's if you know what to look for... When you don't you can always do something like selecting a cell with no shading and typing ? activecell.interior.colorindex in the immediate window. And if you don't know to do that? Turn on the macro recorder and shade a cell and the unshade a cell to get the syntax. _/edit

 

[Asala42]

I ran a quick record macro - I thought that index number was a bit random too - I was like "hokay, I guess '1' would be too obvious" :P