Selecting Sheets by TAB Colour

[koala]

Hello Forum,

I have a workbook with a lot of sheets in it (approx 70).

Different sheet categories have their own colour Tabs.

Some of my macros are only required to run on certain sheets, so therefore I need to be able to select those in a code.

Currently I hard code the names into my macro, but as other sheets sometimes get added, this is not a very good way to do it.

I was wondering if it is possible to select sheets by their TAB colour. (I am running Excel XP).
Alternatively, does anyone know another way that might also work?

Hoping someone is able to assist

Thanks in advance

Koala

[ALe]

You can insert an if condition regarding the colour of the tab.

For example

sub test
dim sheet as worksheet
For each Mysheet in worksheets
if Mysheet.Tab.ColorIndex = 3 'red
Call YourProcedure
end if
end sub
 
Sub YourProcedure
Msgbox "Your procedure has been run"
End sub

[ALe]

sorry there were some mistakes

Sub test
Dim Mysheet as worksheet
For Each Mysheet In worksheets 
    If Mysheet.Tab.ColorIndex = 3 'red
    Call YourProcedure 
    End If 
next Mysheet
End Sub 
 
Sub YourProcedure 
    Msgbox "Your procedure has been run" 
End Sub 

[koala]

ALe,

Thank you for your quick reply.

I had to alter your code a small bit and it works fine

If Mysheet.Tab.ColorIndex = 3  'red

If Mysheet.Tab.ColorIndex = 3 Then 'red

Thank you once again, I will mark it as solved

Koala

[ALe]

You're welcome.
yes, you're right. I forgot "Then". Sorry.