Excel VBA Sort Sheet Tabs by Colour

Get your sheet tabs organised

Using colours on your sheets can help you navigate and organise your file. If you want to sort your sheets by colour you can use a macro to speed up the process.

The macro below goes through each sheet tab in the file and re-orders them based on their color index. In Excel each colour has a number.

Macro Warning: Macros cannot be undone. Macros also clear the Undo list so you can’t undo anything you did before you ran the macro. So always save your file before running a macro so you can close and not save to return to the pre-macro state.

This macro uses the sheet index Sheets(1) to go through the sheets. This is a sequential number that refers to a sheet based on the tab order within the file.  

The x index number may refer to a sheet more than once if the sheets are re-ordered.

In practice the same sheet may be used more than once as it moves to the right as it is compared to the other sheets.

If you remove the apostrophe in front of the MsgBox lines you can see the sheet names being used at each step. You have to click OK on the Message Box when it pops up to progress the macro.      

Sub ShortSheetsByColour()
' sorts sheets based on their color
Dim lCount As Long
Dim x As Long
Dim y As Long
lCount = Sheets.Count
For x = 1 To lCount - 1
    'MsgBox "x = " & Sheets(x).Name
    For y = x + 1 To lCount
        'MsgBox "y = " & Sheets(y).Name
        If Sheets(x).Tab.ColorIndex > Sheets(y).Tab.ColorIndex Then
            'MsgBox Sheets(y).Name & " before:= " & Sheets(x).Name
            Sheets(y).Move before:=Sheets(x)
        End If
    Next y
Next x
End Sub

You can download an example file with the macro at the button below.

Download example file

If you are new to macros check out this post which show you how to use them.


Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.