PDA

View Full Version : [SOLVED] Selecting Sheets by TAB Colour



koala
09-01-2005, 03:47 AM
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
09-01-2005, 03:52 AM
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
09-01-2005, 03:53 AM
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
09-01-2005, 04:13 AM
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
09-01-2005, 04:21 AM
You're welcome.
yes, you're right. I forgot "Then". Sorry.