Hide sheets if colored

xcdave

Board Regular
Joined
Nov 11, 2004
Messages
61
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
Do you mean the Tab Color? Something like this might work for what you're going for:

Code:
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

Board Regular
Joined
Nov 11, 2004
Messages
61
That works great!

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

Dave
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
Object explorer in the VBE. Look up the color constants, specifically, xlColorIndexNone

<sup>edit</sup> 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. <sub>/edit</sub>
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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
 

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top