Macro 22: Group Worksheets by Color
Many of us assign colors to our worksheet tabs. This allows for the visual confirmation that the data
in a certain tab is somehow related to another tab because both have the same color. This macro
groups worksheets based on their tab colors.
How it works
You may think it’s impossible to sort or group by color, but Excel offers a way. Excel assigns an index
number to every color. A light yellow color may have an index number of 36, whereas a maroon color has
the index number 42.
This macro iterates through the sheets in the workbook, comparing the tab color index of the current
sheet to that of the previous one. If the previous sheet has the same color index number as the
current sheet, the macro moves the current sheet before it. By the time all the iterations are done,
all of the sheets are grouped together based on their tab colors.
Sub Macro22()
‘Step 1: Declare your Variables
Dim CurrentSheetIndex As Integer
Dim PrevSheetIndex As Integer
‘Step 2: Set the starting counts and start looping
For CurrentSheetIndex = 1 To Sheets.Count
For PrevSheetIndex = 1 To CurrentSheetIndex – 1
‘Step 3: Check Current Sheet against Previous Sheet
If Sheets(PrevSheetIndex).Tab.ColorIndex = _
Sheets(CurrentSheetIndex).Tab.ColorIndex Then
‘Step 4: If Move Current sheet Before Previous
Sheets(PrevSheetIndex).Move _
Before:=Sheets(CurrentSheetIndex)
End If
‘Step 5 Loop back around to iterate again
Next PrevSheetIndex
Next CurrentSheetIndex
End Sub
1. Step 1 declares two integer variables. The CurrentSheetIndex holds the index number
for the current sheet iteration, and the PrevSheetIndex variable holds the index number
for the previous sheet iteration.
2. Step 2 starts iteration counts for both variables. Note that the count for the PrevSheetIndex
is one number behind the CurrentSheetIndex. After the counts are set, the macro starts
looping.
3. In Step 3, the macro checks to see whether the color index of the previous sheet is the same
as that of the current sheet. Note the use of the Tab.ColorIndex property.
4. Step 4 is reached only if the color index of the previous sheet is equal to the color index of
the current sheet. In this step, the macro uses the Move method to move the current sheet
before the previous sheet.
5. In Step 5, the macro goes back around to the start of the loop. Every iteration of the loop
increments both variables up one number until the last worksheet is touched. After all of
the iterations have run, the macro ends.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it is named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, it doesn’t exist yet. You’ll have to record a
macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-
down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded
macro and replace it with this one.
