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.
If you are new to macros check out this post which show you how to use them.