In your workbook, press
Alt-F11 . If you can't see the VBA Project tree view, press
Ctrl-R. Go
Insert >
Module: this will create a module probably called
Module1. Double-click the name in the VBA Project tree view and a code window will open up. Replace the contents of this window with the following:-
Code:
[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT]
[FONT=Courier New][SIZE=1]Public Sub SelectByTabColor()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Dim wsNames() As String
Dim wsColor() As Integer
Dim ws As Worksheet
Dim ind As Integer
ReDim wsNames(0)
ReDim wsColor(0)
wsNames(0) = ActiveSheet.Name
wsColor(0) = ActiveSheet.Tab.ColorIndex
For Each ws In ThisWorkbook.Sheets
If ws.Tab.ColorIndex = wsColor(0) Then
ReDim Preserve wsNames(UBound(wsNames) + 1)
ReDim Preserve wsColor(UBound(wsColor) + 1)
wsNames(UBound(wsNames)) = ws.Name
wsColor(UBound(wsColor)) = ws.Tab.ColorIndex
End If
Next ws[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Sheets(wsNames).Select
End Sub[/SIZE][/FONT]
Ensure macros are enabled - save the workbook as a
.xlsm file. When you run this code, it will select every worksheet whose tab color is the same as the worksheet which was selected when you ran the code. To run it, select one of the worksheets whose color you want to select by, then in go
Developer >
Macros, click
SelectByTabColor and
Run.
Shout if you experience any problems.