Free Microsoft Excel 2013 Quick Reference

Set active sheet tab color

In excel 2007, the selected worksheet is highlighted in white/color gradient and the sheet name is in bold. However, when multiple worksheets are selected, only one of them is in bold. More than once now I have edited multiple sheets by mistake (something which microsoft warns about in their help page, quoted below). It was easier to differentiate between selected and unselected sheets in excel 2003. My question therefore is:
Is there any way to configure the highlight color of the selected worksheet, to bright yellow for instance?

"Data that you enter or edit in the active sheet (active sheet: The sheet that you're working on in a workbook. The name on the tab of the active sheet is bold.) is reflected in all selected sheets. These changes might replace data on the active sheet and, perhaps unintentionally, on other selected sheets."

Post your answer or comment

comments powered by Disqus
I have a VBA that changes tab colour but I can only get red (255) all other numbers I found on a colour chart give black how can I get blue or yellow the line I put the number in is Sheet.Tab.Color = 255

I have the option to change the sheet tab color, but it's not black so I
can't click on it. When I open a new excel document I can easily change the
tab colors. Help :-)

I have 1 sheet per tenant with their information and X amount of
tenants. The 4 cells that the macro looks at in each sheet are
different dates. For example Lease End Date is one of them. So 1st part
of the If is if the date of the lease end is today or past today make
the sheet tab Red. If its today +30 make it yellow so i know the lease
is ending. If its neither it makes it white. If one cell is true the
loop stops and jumps to the next sheet

Sub test()

Dim ws As Worksheet, rng
rng = Array(13, 16, 22, 27)
For Each ws In Worksheets
x = Application.Match(ws.Name, Array("AT&T Lease", "as"), 0)
If Not IsError(x) Then
With ws
For i = 0 To UBound(rng)
flag = False
Select Case .Range("b" & rng(i)).Value
Case Is <= Date
''''''.Tab.ColorIndex = 3
Application.Run ("TabRed"): flag = True
Case Is < Date + 30
''''''.Tab.ColorIndex = 6
Application.Run ("TabYellow"): flag = True
Case Else
''''' .Tab.ColorIndex = -4142
Application.Run ("TabWhite"): flag = False
End Select
If flag Then Exit For
End With
End If
End Sub

The application "TabRed" is: ActiveWorkbook.Sheets("AT&T
Lease").Tab.ColorIndex = 3

I think "TabRed" is code i need to change to make this all work
correctly. But i tried to insert: ".Tab.ColorIndex = 3" instead and it
error's on me.

Is there any way to say change the sheet tab color that the loop is on
and not specify a sheet?

Thanks everyone in advance.

Hello all,
Yesterday we were trying to color the sheet tab that matched the date -1 day while trying to return no color for the previous day in Excel 2002. I modified the code and placed these to lines in:

Sheets(Format(Date - 2, "mmmd")).Activate
ActiveSheet.Tab.ColorIndex = xlNone

So the whole code is:

Private Sub Workbook_Open()
On Error Resume Next
On Error GoTo 0
Sheets(Format(Date - 2, "mmmd")).Activate
ActiveSheet.Tab.ColorIndex = xlNone
Sheets(Format(Date - 1, "mmmd")).Activate
ActiveSheet.Tab.ColorIndex = 3
End Sub

I want to thank everyone who helped out.

Why can't I set each sheet tab to a different color? This feature was
available in Lotus 123 V 97. You are going BACKWARDS. If I am wrong about
this, how can it be done???

I searched and found a couple of different active.sheet codes but could not get it to work. What I want to do is move the worksheet tab I create in the code below all the way to the end (right) of the worksheet tabs already in my workbook.

Sub Workbook_Open()
   Dim WS As Worksheet
   Set WS = Sheets.Add
      WS.Name = Format(Date, "mm-dd-yy")
End Sub
Much thanks,


I am just beginning to get into VBA and macro coding so please excuse the ignorance. I know this has to be a simple macro but I cannot figure this out through recording macros or anything. I have tabs in a workbook that are numbered from 1 to 31 for each day of the week. I would like to be able to, in any sheet, activate a cell, press a button and have a row from a reference sheet copied and inserted at the active cell.

The macro I have in mind would perform the following when an "INSERT ROW" button was pressed on the active sheet.

set active sheet
set active cell or row
go to sheet1, copy row 14
return to active sheet
insert/paste row at active cell/row

Any help is appreciated.

I'm currently using this code to change tabcolor:

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
    If Target.Name.Name = Sh.Name & "!TabColor" Then 
        Select Case Target.Value 
        Case "Print": Sh.Tab.Color = vbGreen 
        Case "No": Sh.Tab.Color = vbRed 
        End Select 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is from - thank you Bill.

It works great if I hardcode the cells that drive the color, but not if I use a formula in those cells. The formula I'm using is a simple IF statement - IF(B1+C1=0,"No","Print")
This workbook will be used by hundreds so I need a formula in there to produce location specific results (whether or not to print).
Also, I will be putting the formula in a specific cell (say H1) on every sheet, if that makes a difference.
Does anyone have any ideas for me?

Thank you for all the help in the past!

Good Morning: I have the following code in Worksheet Deactivate

ActiveWorkbook.Sheets("Members").Tab.ColorIndex = 3 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
. This works fine except when I try to eliminate ("Members") & use ("Sheet1") the code fails. I have tried

ActiveWorkbook.Sheet1.Tab.ColorIndex =3 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex =3 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
& it fails. Is there a way to just use the sheet # & not the sheet Name to change the Tab Color? I have sheets in my workbook that the name changes under certain conditions & using the sheet name will ultimately make the code fail as well.
Thank You

In Excel 2007 when you change the color of the tab it gives you that color but with a transition from dark on the bottom to lighter on the top. When that sheet is selected it transitions from the color you chose all they way to white.
One of our users is complaining that this fading of the colors is making it difficult for him to read the sheet name. Is there any way to make Excel 2007 display the tab colors as solid colors instead of this light to dark transition effect?

A user would like to change the background color of the sheet tab... the part
where the sheet name goes, where it says 'Sheet 1', 'Sheet 2', etc.

Is this possible in Excel? If so, how?


Hi! I really need help with changing a worksheet tab color based on a specific variable inside my worksheet. I have attached the workbook. In this workbook, There is a summary page for all open and closed projects. Between these 2 sheets are individual project sheet tabs for each individual project. i would like to be able to automatically change the tab color (light brown color) for each individual project based on row 3, column C , when "Closed" is chosen from the drop down list. I have a "project template" that is used for new projects, but would like this function to be in the already existing projects (263344, 261089, 11-422, etc....).

Any help would be greatly appreciated!
Thank you!

I use an Excel spreadsheet form to track various landscaping projects. I
denote the status of the project by using colors assigned by conditional
formatting based on the value of a status drop-down listbox on the sheet,
which works great, but I'd also like the tab color of the sheet to match the
status the various status colors I've chosen. Is there a way to do this?

I have been trying to use the data from an open worksheet (via a print button) to populate another worksheet - print the populated worksheet - clear - and then remain on the active sheet

I have the bones of it I think but I cannot find how to call the active worksheet wsSht1

this is within an open workbook

any ideas appreciated

Sub Button1_Click()

Dim wsSht1 As Worksheet, wsSht2 As Worksheet

Set wsSht1 = Workbooks("TRAILER MAINTENANCE ISSUES2.xls").ActiveSheet
Set wsSht2 = Workbooks("TRAILER MAINTENANCE ISSUES2.xls").Sheets("WALKING FLOORS")

wsSht2.Range("f3").Value = wsSht1.Range("c2").Value
wsSht2.Range("m3").Value = wsSht1.Range("e4").Value
wsSht2.Range("l4").Value = wsSht1.Range("f7").Value
wsSht2.Range("b6:b10").Value = wsSht1.Range("d12:d16").Value

wsSht2.PrintOut Copies:=1, Collate:=True

wsSht2.Range("f3").Value = ""
wsSht2.Range("m3").Value = ""
wsSht2.Range("l4").Value = ""
wsSht2.Range("b6:b10").Value = ""
End Sub

Yeah, Ok... How ?? it is not on the menu for "Format"> Sheet > Tab Color

"Gord Dibben" wrote:

> Colored Tabs in Excel 2003 also.
> Gord Dibben Excel MVP
> On Tue, 17 May 2005 10:35:03 -0700, MarthaSue
> <> wrote:
> >Hi Chip,
> >
> >What about in Excel 2003
> >
> >"Chip Pearson" wrote:
> >

I have a macro that changes the active sheet tab name to the text in a specific cell.My question is there a way to change the code so it changes the tab name with reference to two seperate cells maybe with a dash inbetween the two cell references.In this case I would like to reference tab name to (q7)&(q8)
Thanks in advance

I'm unable to view sheet tabs nor the horizontal scrollbar. I have verified the options are set to sheet tabs & horiz scrollbar in the options menu. Also, I verified I'm opening the file with the Excel app. Are there anything else I should check ? I would appreciate any help on this since I'm at my wits' end.

Add: It seems as if though my spreadsheet is sized too large for me to see the scrollbars & tabs. The vertical scrollbar top arrow is visible, but not the bottom arrow. If I keep scrolling down, the vertical bar decreases in size and then disappear from view on bottom.


I am trying to use a function to set the tab color, such as the following:

    ActiveSheet.Tab.ColorIndex = Tcolorcode 
    Tcolor = "" 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But if this function is called due to a change on a worksheet other than the one where the Tcolor function is called, it seems to change the color on the tab of the currently-active worksheet. This is probably what would be expected by my using Activesheet in the code. Is there a way I can access the sheet (and/or the worksheet name) for the worksheet from which this function is called, not the currently-active one?

I have a module in vba that creates new PO tab in excel using an add-in menu. I have posted the code here. What I want to know is if I can change the tab color to red everytime it creates a new PO tab. Any help would be greatly appreciated. Please let me know if you need any more information.

    Dim WrkShtNm As String 
    Dim WrkBookNm As String 
    Dim QuoteCnt As String 
    Dim iCnt As Integer 
    Dim ShtIndex As Long 
    Dim WBPOTemp As Workbook 
    Dim JobName As String 
    Dim TmpFile As String 
    Dim POUser As String 
     'Dim POSht As Worksheet
     'Dim WkSht As Worksheet
    If Supplier = "OTHER" Then 
        Supplier = InputBox(" Type SUPPLIER Name", "Get Supplier Name") 
    End If 
     'MsgBox (Supplier)
    Application.ScreenUpdating = False 
    Set WkSht = ActiveSheet 
     'WrkBookNm = ThisWorkbook.FullName
     'MsgBox (WrkBookNm)
    Set WBPOTemp = Application.Workbooks.Open 
     'MsgBox (ActiveSheet.Name)
    QuoteCnt = "" 
    For iCnt = 1 To Len(ActiveSheet.Name) 
        If IsNumeric(Mid(ActiveSheet.Name, iCnt, 1)) Then 
            QuoteCnt = QuoteCnt & Mid(ActiveSheet.Name, iCnt, 1) 
        End If 
    Next iCnt 
    WrkShtNm = "PO" & QuoteCnt 
     'MsgBox (WrkShtNm)
    ShtIndex = Worksheets(ActiveSheet.Name).Index 
     'MsgBox (ShtIndex)
    ActiveWorkbook.Sheets.Add after:=Worksheets(ShtIndex) 
    Set POSht = ActiveSheet 
    POSht.Name = WrkShtNm 
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row 
    LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column 
    WkSht.Range(Cells(9, 1), Cells(LastRow, LastColumn)).Copy 
    ActiveWorkbook.ActiveSheet.Range(Cells(9, 1), Cells(LastRow, LastColumn)) _ 
    .PasteSpecial Paste:=xlPasteAll 
     '.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Application.CutCopyMode = False 
    WkSht.Range(Cells(1, 1), Cells(5, LastColumn)).Copy 
    ActiveWorkbook.ActiveSheet.Range(Cells(1, 1), Cells(5, LastColumn)) _ 
    .PasteSpecial Paste:=xlPasteAll 
    Application.CutCopyMode = False 
    POSht.Range("A:G") _ 
    .PasteSpecial Paste:=xlPasteFormats 
    Application.CutCopyMode = False 
     'MsgBox ("Formats Done")
    With ActiveWorkbook.Sheets(ShtIndex + 1) 
        .Range("A5").RowHeight = 17.4 
        .Range("A6").RowHeight = 17.4 
        .Range("A7").RowHeight = 17.4 
        .Range("A8").RowHeight = 17.4 
        .Range(Cells(10, 1), Cells(LastRow, 7)).RowHeight = 11.5 
    End With 
    With POSht.PageSetup 
        .TopMargin = 26 
        .LeftMargin = 18 
        .BottomMargin = 0 
        .RightMargin = 8 
        .FooterMargin = 18 
        .LeftFooter = WBPOTemp.Sheets(1).PageSetup.LeftFooter 
        .CenterFooter = WBPOTemp.Sheets(1).PageSetup.CenterFooter 
        .RightFooter = WBPOTemp.Sheets(1).PageSetup.RightFooter 
        .Zoom = False 
        .FitToPagesWide = 1 
        .FitToPagesTall = 1 
    End With 
     'MsgBox ("Margins Done")
    ShpCnt = WBPOTemp.Sheets(1).Shapes.Count 
    For Scnt = 1 To ShpCnt 
        Shptop = WBPOTemp.Sheets(1).Shapes(Scnt).Top 
        ShpLeft = WBPOTemp.Sheets(1).Shapes(Scnt).Left 
        POSht.Shapes(Scnt).Top = Shptop 
        POSht.Shapes(Scnt).Left = ShpLeft 
    Next Scnt 
     'Fill out Form
    JobName = ThisWorkbook.Name 
    TmpFile = Mid(JobName, 1, InStr(JobName, " ") - 1) 
    JobName = TmpFile & "-" & (QuoteCnt) 
    If ActiveSheet.Range("E10").EntireColumn.Hidden = True Then 
        With ActiveWorkbook.Sheets(ShtIndex + 1) 
            .Range("C5").Value = "Supplier" 
            .Range("C6").Value = "Purchase Order #" 
            .Range("C7").Value = "Attn:" 
            .Range("C5").HorizontalAlignment = xlRight 
            .Range("C6").HorizontalAlignment = xlRight 
            .Range("C7").HorizontalAlignment = xlRight 
            .Range("F5").Value = Supplier 
            .Range("F6").Value = JobName & "-" & (Date) 
            .Range("F6").Font.Size = 12 
            .Range("F70:G70").BorderAround Weight:=xlMedium, ColorIndex:=1 
            .Range("F70:G70").NumberFormat = "mm/dd/yyyy" 
            .Range("F70:G70").Font.Size = 12 
            .Range("F70:G70").Font.Bold = True 
            .Range("F70:G70").HorizontalAlignment = xlCenter 
        End With 
        With ActiveWorkbook.Sheets(ShtIndex + 1) 
            .Range("E5").Value = "Supplier" 
            .Range("E6").Value = "Purchase Order #" 
            .Range("E7").Value = "Attn:" 
            .Range("F5").Value = Supplier 
            .Range("F6").Value = JobName & "-" & (Date) 
            .Range("F6").Font.Size = 12 
            .Range("F70:G70").BorderAround Weight:=xlMedium, ColorIndex:=1 
            .Range("F70:G70").NumberFormat = "mm/dd/yyyy" 
            .Range("F70:G70").Font.Size = 12 
            .Range("F70:G70").Font.Bold = True 
            .Range("F70:G70").HorizontalAlignment = xlCenter 
        End With 
    End If 
    POUser = Environ("USERNAME") 
     'MsgBox (Application.Proper(POUser))
    POUser = Application.Proper(POUser) 
    ActiveSheet.Shapes("Rectangle 4").Select 
    Selection.Characters.Text = POUser & "  " & Date 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlCenter 
        .Orientation = xlHorizontal 
        .AutoSize = False 
    End With 
    WBPOTemp.Close False ' close the source workbook without saving any changes
    Set WBQTemp = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I currently have code set up to copy data from one workbook to another, as follows:

Workbooks.Open Filename:="J:AdministrationAccountingFUNDSFUNDS-DailiesCurrentEagleIFF" & year2 & month1 & day2 &

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is that the macro is copying data from whichever sheet was the active sheet when the source file was saved. I want it to set the active sheet to the correct sheet, no matter how the source file was saved.

I am breaking on the highlighted line. Do you know what the problem is?

Workbooks.Open Filename:="J:AdministrationAccountingFUNDSFUNDS-DailiesCurrentEagleCAAT" & year2 & month1 & day2 &
[COLOR=lime]ActiveWorkbook.Sheets("Working Trial Balance").Range("B10:P110").Select[/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi. First post. I did do some browsing and searching first. Couldn't find what I needed. But I'm going to go with the assumption that it's me.

I'm trying to set up a macro with radio buttons that will automatically color the sheet tabs. I found a macro that will do this, but it only works on Sheet1. I'm def not brilliant enough to figure out how to make it work across all tabs. This is on a tracking sheet that grows by 10-15 separate sheet (tabs) entries per day. Color coding the tabs helps us in identifying status. Much appreciated. Here's my current code:

Sub SelectBlue()
    ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 11
End Sub

Sub SelectGreen()
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 6
End Sub
Sub SelectRed()
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 8
End Sub

hey everyone

i'm using this code below to color sheet tab if string "9.9999" found in column "A" on an excel sheet.

Dim ws As Worksheet
Dim wb As Workbook
FoulTime = "99999"
Dim rng As Range

Set wb = ThisWorkbook
For Each ws In wb.Worksheets
    Set rng = ws.UsedRange.Find(FoulTime)
        If rng Is Nothing Then
        ws.Tab.ColorIndex = 4
        ws.Tab.ColorIndex = 3
              End If
    Next ws
recap: if "9.9999"found Color Tab = Red, if not Green.
The problem is what if wanted the Color to become red if both strings "9.9999" OR/AND "0.0000" found?

how do i do ?
thanks in advance

Hello all,

I have a workbook with Several sheets with custom names. What I would like to do is create VBA that when data in the active sheet is changed (A2:A20), it will change the color of the sheet tab to red (255). I need this to work for all the sheets in the workbook. Can you help me out?

Thanks in advance

Is there a way to reference a sheet name as a variable from a cell?

For instance, I am trying to set up a way to color the sheet tab based on what name is input in say Cell A1. So if "sheet1" is selected (from a list of sheetnames), then sheet1 tab color would change to red. Is this possible?

No luck finding an answer? You could always try Google.