macro to select worksheet based on tab color

**drewbizz** · 02-03-2012, 04:46 PM

Hi can anyone help me create a macro that selects all worksheet tabs of a particular color. For instance, if i am selecting a worksheet that the tab is colored red, what is the vba code to select all red tabs in the workbook?

**john55** · 02-03-2012, 05:35 PM

just try...

Sub Button1_Click()
 Dim ws As Worksheet
     
    For Each ws In Worksheets
        If ws.Tab.Color = vbRed Then ws.Visible = xlSheetHidden
    Next ws
End Sub

and

Sub Button2_Click()
Dim ws As Worksheet
     
    For Each ws In Worksheets
        If ws.Tab.Color = vbRed Then ws.Visible = xlSheetVisible
    Next ws
End Sub

**JieJenn** · 02-03-2012, 05:37 PM

replace the number with your own color number

Sub Macro2()
Dim ws As Worksheet


For Each ws In ThisWorkbook.Worksheets
    If ws.Tab.Color = 192 Then
    ws.Select (False)
    End If
Next ws
  
End Sub

You can find out about the tab color by using

msgbox activesheet.tab.color

**drewbizz** · 02-03-2012, 10:18 PM

What if I want the macro to be written so that if I click on a tab of any color all tabs of that color.in the workbook are selected. Thx for ur help:D

**JieJenn** · 02-04-2012, 10:55 AM

In that case put this in your workbook module. I use a conditional check box to turn on/off macro. In my case, if worksheet macro on and off range A1 = True then macro is active, if A1= False then macro is inactive.

Anyone know why I am having issue with checkbox active x control? If I use checkbox as a conditional check if won't let me go into Design mode unless I select other tab first.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Worksheets("Turn on off macro").Cells(1, 1) = True Then
Call Test
End If

End Sub

Sub Test()
Dim Ws As Worksheet

For Each Ws In ThisWorkbook.Worksheets
    If Ws.Tab.Color = ActiveSheet.Tab.Color Then
    Ws.Select (False)
    End If
Next Ws

End Sub