How to "reset" tabbed sheet colors back to normal?

Eloise T · Oct 1, 2015

I have an Excel 2007 spreadsheet with many tabbed sheets that I have to access daily. Thinking I would be "awesome" in Excel, I right-clicked the tabs, selected Tab Color and changed the tab color to "mark" the fact that I had done what was necessary on the sheets for the day and would not have to look at them again until tomorrow. The other people (who are higher up the food chain) sharing the spreadsheet didn't appreciate my desire to be awesome and requested the color(s) (both font and background) be changed back to normal. I have not found the trick to getting them back. Help!

NARAYANK991 · Oct 1, 2015

Hi ,

Surely all you have to do is go through the same menu , selecting No Color this time ?

Narayan

vletm · Oct 1, 2015

You wanted tab-colours?
and
'Bosses' don't?
... Gotta do Macro like =>

sub Tab_Colour()
Sheets(1).Tab.ColorIndex = 4
Sheets(2).Tab.ColorIndex = 5
Sheets(3).Tab.ColorIndex = 6
end Sub

You can load Tab-colour 'on' then You'll use file and
then Your 'boss' uses ... reset Tab-colours.
Ask more, if You need, Okay?

Eloise T · Oct 1, 2015

Narayan, I saw No Color and made the false assumption that it was truly NO COLOR. Microsoft should rename it to default color. Thanks for your help!

vletm, I tried using the macro by keying Alt-F11, F7, pasting your code, then F5 to run, but got "Compile error: Syntax error." I copied and pasted without editing anything. Did I leave out a step?

vletm · Oct 1, 2015

Okay ...
I didn't tell 'everything'.
You should do few more things...
Check that 'sample'-file.
Test both buttons.
If works ... we'll continue, Okay?

Eloise T · Oct 1, 2015

vletm,
There are no buttons. I simply get "Compile error: Syntax error."

bobhc · Oct 1, 2015

@Whatever (now there a descriptive handle)

There are two buttons on sheet 1, one called "WHAT EVER" and one labeled "BOSS" If you click these buttons, which are quite large, your sheet tabs will change as you wanted them, @vletm has done the job you asked.

.

vletm · Oct 1, 2015

Can You take any kind of screenshot?
I would like to see the whole Excel-window.
... and post it here.
btw. Can You use .xlsm -files?

Eloise T · Oct 1, 2015

Screen shot uploaded. I do not know how to create a .xlsm file.

vletm · Oct 1, 2015

Did You test that 'whatever.xlsm'-file?
1) Open it
2) test both buttons

You cannot 'just try to move' Sub! ... or You can,
but then ... I think that You have copied some 'hidden marks too'.
Normally in the end of line.

Anyway. That You screenshot-file is from another file.
and You have 'copied' that Sub in wrong place!
and if You really want to use 'Your/Boss' -mode, please follow orders.

Small steps. Okay?

Eloise T · Oct 2, 2015

I apologize. I did not see the whatever.xlsm file until moment ago when I scrolled back through the dialogue. The low contrast escaped my notice...which is why I have not upgraded my iPhone 4s to iOS 7 or 8...both are low contrast. ANYWAY, I'm on track with you now.

When I click on the buttons, it changes the sheet colors from colorful to default blue. I assume you accomplished this with the

sub Tab_Colour()
Sheets(1).Tab.ColorIndex = 4
Sheets(2).Tab.ColorIndex = 5
Sheets(3).Tab.ColorIndex = 6
end Sub

What I don't know is where that lives in the spreadsheet and how I can implement it into mine.

Thanks.

P.S. As a side note, while I am experimenting with this, is there a Copy/Paste method I can use to make additional sheets using the same formatting, cell sizes, and general layout. I assume I copy the headers, etc. and use Paste Special? I have not figured out if that is correct and if so, which radio button under Copy Special to select as none seem to do the job.

Thank you in advance.

vletm · Oct 2, 2015

Let's continue ... read all => act
1) Open that file, You want to use Your TAB-colors and MAKE A BACKUP!
2) Open 'WhatEver.xlsm'-file below and save to same folder #1-file.
3) Check that 'File' -cell is correct (same than #1 -file)
Every mark matters!
4) Close 'that file' (same than #1 -file)
5) Activate 'WhatEver.xlsm' by clicking somewhere (not button!)
6) Press [ GET COLORS ]
7) Save 'WhatEver.xlsm' and send it back for next step!
if any/some nonwanted notes comes ... record for me too.

Do not press [ Set WhatEver TAB-colors ]-button!

and Your P.S.
... many things are possible, but we'll try to do TAB-colors 1st, Okay?

Eloise T · Oct 2, 2015

I hope this is what you were looking for.

vletm · Oct 3, 2015

I have to think that You did that with my instructions ...
... different number of tabs, names of tabs are different than Your 'photo' except only same names and
... 'User_E' and 'User_A' are same. It's possible, but never seen before that.
Anyway, 'last save by' match with 'User_A'-information. That's good.
My previous idea was that those tab names and places would be fixed...
Are those 'same names' Your special colour tabs?
Or how do You do choose those tabs?
I can make the Macro that every time You'll open that file,
those tabs (or Your named tabs) will be in Your right color.
All another tabs will be default color.
... and if anybody else opens that file, all tabs will be default color.
Of course, You have to name the color of tabs You want to use
Is it possible, that 'File' could change to / use with '.xlsm'-ending?
Or gotta use plan C?

Eloise T · Oct 4, 2015

Referring back to my original post, "I right-clicked the tabs, selected Tab Color and changed the tab color to 'mark' the fact that I had done what was necessary on the sheets for the time I had possession of the spreadsheet file and would not have to look at the colored tabs again until the next time the spreadsheet landed back 'on my desk.' The other people (who are higher up the food chain) sharing the spreadsheet didn't appreciate my desire to be 'awesome in Excel' and requested the color(s) be changed back to normal."

In other words, I will open the spreadsheet with no colored tabs, change the tabs to any color after making any updates (I'll probably choose red or green), and then when my editing is done, change all the colored tabs back to normal before handing the spreadsheet file off to the next person that needs to manipulate the data. Clicking one "button" to set the tab colors back to default before passing the file on to the next person would be great. So really, all that the macro needs to do is reset all the colors back to default.

vletm · Oct 4, 2015

If Your needs is 'only' back to defaults ... Okay
Here is the macro for You.

Code:

Sub Default_Tabs_Colors()
    Application.ScreenUpdating = False
    s_tab = Worksheets.Count
    For s = 1 To s_tab
        Sheets(s).Tab.ColorIndex = xlNone
    Next s
End Sub

Deepak · Oct 4, 2015

Will you both take the liberty to tag the code,pls.

Eloise T · Oct 5, 2015

How do I engage the macro?
Alt +F11
F7
Paste:

Code:

Sub Default_Tabs_Colors()
  Application.ScreenUpdating = False
  s_tab = Worksheets.Count
  For s = 1 To s_tab
  Sheets(s).Tab.ColorIndex = xlNone
  Next s
End Sub

F5?
Then what?

Eloise T · Oct 5, 2015

Deepak said:
Will you both take the liberty to tag the code,pls.

Tag the code?

Deepak · Oct 5, 2015

Whatever said:
Tag the code?

use code tags before & after the code.

vletm · Oct 5, 2015

@Whatever
1) Open that file You want to control Tabs-colors.
2) Tools -> Macro -> Visual Basic Editor
3) Insert -> Module
4) Copy 'my code' to that Module
5) Close Module-window
6) Activate Excel-window
7) File -> SaveAs -> choose Format: Macro-Enabled Workbook (.xlsm)
8) Press [ Save ]
9) Use this file!
10) To Run ...
11) Tools -> Macro -> Macros...
12) Press [ Run ] ( my default is that, there is only one Macro )
13) All Tabs should be default color ( like 'none' )

Eloise T · Oct 5, 2015

Thank you.
The top (header) line of each tabbed sheet is "frozen" with Freeze Panes.
I have created a button: Insert > Shapes > "square button" in the frozen area.
I would like to press/click the button and have it automatically color the tab red.
When I'm done with my part of the editing I will click on the COLOR OFF button and pass the spreadsheet file onto the next person. How do I add that?

Eloise T · Oct 5, 2015

Deepak said:
use code tags before & after the code.

View attachment 22969

Can you give an example?

bobhc · Oct 5, 2015

When you go to post a new question you will see a widow above the text options.
By putting "[CODE"]"" before and "[/CODE"]" after your code you get the following, (no double quotes.)

Code:

sub Tab_Colour()
Sheets(1).Tab.ColorIndex = 4
Sheets(2).Tab.ColorIndex = 5
Sheets(3).Tab.ColorIndex = 6
end Sub

Eloise T · Oct 5, 2015

See attached file to see if this is the correct macro. Thank you.
...or is there a more efficient way to do this?
P.S. I accidently left out:

Code:

Sub Tab_10_Color_ON()
  Application.ScreenUpdating = False
 
  s_tab = Worksheets.Count

  Sheets(10).Tab.ColorIndex = 3
 
End Sub

How to "reset" tabbed sheet colors back to normal?

Active Member

Excel Ninja

Excel Ninja

Active Member

Excel Ninja

Attachments

Active Member

Excel Ninja

Excel Ninja

Active Member

Attachments

Excel Ninja

Active Member

Excel Ninja

Attachments

Active Member

Attachments

Excel Ninja

Active Member

Excel Ninja

Excel Ninja

Active Member

Active Member

Excel Ninja

Excel Ninja

Active Member

Active Member

Excel Ninja

Active Member

Attachments