Solved Macro for changing Tab Colors in Excel

Microsoft Office excel 2007 home & stude...
December 16, 2011 at 16:26:16
Specs: Windows XP
I was hoping someone can help me with a Macro.

I have an Excel Spreadsheet that has numerous tabs. Some tabs are different colors. I was hoping for a Macro that can search the entire workbook and change the tab colors automatically.

For Example:
Check the workbook for all tab colors. All tab colors that are "orange" change the color to "no color".

Hope this makes sense. Thanks in advance for any help!


See More: Macro for changing Tab Colors in Excel

Report •


✔ Best Answer
December 19, 2011 at 16:38:40
The grid I get in 2010 has an Orange called "Orange, Accent 6" in the upper right corner and then below that 5 different TintAndShade percentages for "Orange, Accent 6".

In addition, there is an "Orange" in the section entitled "Standard Colors".

If the Orange you are trying to change is from the "Orange, Accent 6" section, you can try this code:

Sub NoOrangeAccent()
 For shtNum = 1 To Sheets.Count
  If Sheets(shtNum).Tab.ThemeColor = xlThemeColorAccent6 Then
   Sheets(shtNum).Tab.Color = xlNone
  End If
 Next
End Sub

If the Orange is from the Standard Colors choices, try this:

Sub NoOrangeColor()
 For shtNum = 1 To Sheets.Count
  If Sheets(shtNum).Tab.Color = 49407 Then
   Sheets(shtNum).Tab.Color = xlNone
  End If
 Next
End Sub

Of course, if you want to cover both cases, try this:

Sub NoOrange()
 For shtNum = 1 To Sheets.Count
  If Sheets(shtNum).Tab.Color = 49407 Or _
     Sheets(shtNum).Tab.ThemeColor = xlThemeColorAccent6 Then
       Sheets(shtNum).Tab.Color = xlNone
  End If
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
December 16, 2011 at 17:51:16

Report •

#2
December 19, 2011 at 10:04:02
I am running Microsoft Excel 2007

Report •

#3
December 19, 2011 at 13:23:10
Well, I wish your goal was as simple as it sounds, but Excel 2007 makes it a bit more difficult.

In 2003, there was one ColorIndex number for Orange, so checking for that number and changing it was easy.

In 2007 and beyond, you have to start worrying about multiple Oranges as well as Tint and Shading values.

It might be easy, but without knowing the exact color value(s) for the tabs you want to change, it's tough to just toss out some code.

Do you know how to use VBA to check the values for the Tab colors?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 19, 2011 at 14:45:41
I do not know how to use VBA to check the values for Tab colors. If you can give me a code, I can try and run it to tell you more information. Is the color index for font different than the color index for the tabs? I'm assuming that the color index is 45 or 46.

When I right click on the the Tab (sheet) and select Tab Color...a grid comes up. It would be the orange color on the right side of the grid. I would like to change that orange to the "No Color" selection right about the selection for "More Colors"

I hope that makes sense and helps you out.


Report •

#5
December 19, 2011 at 16:38:40
✔ Best Answer
The grid I get in 2010 has an Orange called "Orange, Accent 6" in the upper right corner and then below that 5 different TintAndShade percentages for "Orange, Accent 6".

In addition, there is an "Orange" in the section entitled "Standard Colors".

If the Orange you are trying to change is from the "Orange, Accent 6" section, you can try this code:

Sub NoOrangeAccent()
 For shtNum = 1 To Sheets.Count
  If Sheets(shtNum).Tab.ThemeColor = xlThemeColorAccent6 Then
   Sheets(shtNum).Tab.Color = xlNone
  End If
 Next
End Sub

If the Orange is from the Standard Colors choices, try this:

Sub NoOrangeColor()
 For shtNum = 1 To Sheets.Count
  If Sheets(shtNum).Tab.Color = 49407 Then
   Sheets(shtNum).Tab.Color = xlNone
  End If
 Next
End Sub

Of course, if you want to cover both cases, try this:

Sub NoOrange()
 For shtNum = 1 To Sheets.Count
  If Sheets(shtNum).Tab.Color = 49407 Or _
     Sheets(shtNum).Tab.ThemeColor = xlThemeColorAccent6 Then
       Sheets(shtNum).Tab.Color = xlNone
  End If
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 19, 2011 at 16:58:14
This is totally awesome and worked like a charm! You are a life saver!

Report •


Ask Question