Free Microsoft Excel 2013 Quick Reference

VBA Macro Code To Change Sheet Tab Color

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


Good Morning: I have the following code in Worksheet Deactivate

	VB:
	
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

	VB:
	
ActiveWorkbook.Sheet1.Tab.ColorIndex =3 

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

	VB:
	
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
abbeville

XL2000: Sample Macro Code to Change Case of Text

Although Microsoft Excel has functions that change the case of text, there are times when you may want to change text case programmatically. This article contains three sample ...

Sample macro code to change the case of text in Excel

Although Microsoft Excel has functions that change the case of text, there are times when you may want to change text case programmatically. This article contains three sample ...

XL2002: Sample Macro Code to Change Case of Text

Does anyone know the VBA code to change the fill color of a series on an area chart? Excel 2007 Macro Recorder doesn't seem to print it the line of code when trying to record manual color change.

Hi, this might be a simple thing to do but not for me.

I would like to have a vba code that changes the cell color so that if there is number 1 (just number 1) in some cell the background of the cell changes in to shade of grey and cell that has number 2 changes into darker shade of grey and so on. I have numbers 1-10 in random order in my sheet. I mean there is many cells that has the same number and the sheet is quite large so vba code would be ideal choise to do it quickly.

is this possible.

Does anyone know the vba code for changing a tab to the current date?
I have the code to create a string that is the current date, and have
applied that to other things, such as a new file name. I can't seem
to find how to apply that as a tab name.

Thanks.

--
TurboBeagle
------------------------------------------------------------------------
TurboBeagle's Profile: http://www.excelforum.com/member.php...o&userid=23415
View this thread: http://www.excelforum.com/showthread...hreadid=384694

Does anyone know the vba code for changing a tab to the current date? I have the code to create a string that is the current date, and have applied that to other things, such as a new file name. I can't seem to find how to apply that as a tab name.

Thanks.

Staff,
Anyone know if it is possible to change the project password via vba code?
Imagine a function that on a certain date he change the password of the project. Is it possible?

Basically what I am trying to do is. I have a workbook with about 12 worksheets within it. I am trying to input a button on worksheet 3 that when pushed would activate worksheet 12. Basically the same as if you clicked on the tab at the bottom of the screen called worksheet 12.

At this point I am lost. Could someone help me. I realise that you have to create a button which Ive done. However I cannot seem to figure out the proper macro code to get the button to change the current worksheet when its pushed. Any ideas?

I'm using the following code to change the color of an OptionButton (in a userForm) from black to blue when the OptionButton is selected and it works fine.

If OptionButton2.Value = True Then
OptionButton2.ForeColor = &HFF0000
Else
OptionButton2.ForeColor = &H80000012
End If

But for some reason it doesn't work when I substitute the blue for orange.
The color I want is &H000080FF&, but the VBA code defaults to:
OptionButton2.ForeColor = &H80FF

And gives me an error: "Run Time Error '380. Could not set ForeColor property value. Invalid property value"

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?

Thanks.
Mark

Hi
I am working on an Excel spreadsheet that we want to use for problem tracking. I have created tabs for each location that reports - sample spreadsheet attached. I want the tab color to change dependent on data on the sheet

Each problem will be on a separate line.
If a problem has been logged but not acknowledged, then the tab should be red.
If the problem has been acknowledged, then the tab should be amber.
If the problem has been closed, then the tab should be green.

Simplifying it a little (you can't close a problem if it hasn't been acknowledged!) I managed to get something working using if and counta to change the contents of a call with the color I want:
=IF(COUNTA($A:$A)=COUNTA($E:$E),"green",IF(COUNTA($A:$A)=COUNTA($C:$C),"amber","red"))

How do I translate this into code to change the tab color for each worksheet?

Many thanks!

Robert

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!
Stacy

I've got my vba/macro code running correctly in one workbook. What is the best way to add/attach that code to many workbooks? (Use a template?)

The code is linked to a button and I don't want to add the code and button manually to many workbooks.

Any ideas are greatly appreciated. Thank you.

I have a long list of tabs listing "projects" which have changing names - on the first sheet, I want to have the table of contents automatically update and link to each tab - I want the user to only have to change the tab name to have the table of contents and link update - any help?

Hello,

What I want to achieve with my current workbook is to retrieve input data from a source file. This needs to be done because active workbooks need to have most current data within there comboboxes. My current macro is to change the current worksheet to copy data from a 'input' file. The problem I'm coming into is if the file exists i get a prompt to ask to override. What I'd is a way to by pass this prompt to override an existing file, or a cleaner method then this shows.


	VB:
	
 getthesource () 
    activesheet.Copy after:=Sheets(Sheets.Count)ActiveWorkbook.SaveAs Filename:="processing" 
    activesheet.Name = "working" 
    Workbooks.Open Filename:="Users:matthewchrycy:desktop:input.xlsx" 
    Application.EnableEvents = False 
    Workbooks("input.xlsx").Sheets("input").Copy after:=Workbooks("processing").Sheets("working") 
    Application.EnableEvents = True 
    Windows("processing").Activate 
    ActiveWorkbook.Sheets("working").Select 
    activesheet.Name = Format(Date, "mmm-dd-yyyy") 
    ActiveWorkbook.SaveAs Filename:=Range("d3") 
End Sub 

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

Hello, I have the code below set to change the background color based on certain conditions. What I need to do is change it so that if none of the conditions are met, it does not change the color at all. As it is written now, if anything is entered in a cell that has a background already set, but does not meet these conditions, the cell turns white.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

 On Error GoTo ErrorHandler
 
 
'change the conditions below
  Select Case Target.Value
           Case "OFF"
                icolor = 35
            Case "RTO"
                icolor = 8
            Case "vacation"
                icolor = 8
            Case "10-9 SBP"
                icolor = 38
            Case Else
        End Select

 
  Target.Interior.ColorIndex = icolor
ErrorHandler:
    On Error Resume Next
    
 

End Sub

Thanks!
Clayton Grove

I am trying to use vba (excel 2007) to change the tab color depending on the value of a specific cell.

Example: Change tab color to red, if cell A1 = <2.5. Change tab color to green, if cell A1 = >2.5 but <4.

Any ideas?

Ryan

Is it possible to change the tab colors for different worksheets? I saw
a posting but couldn't read the answers because they were posted in
2004. Any help is appreciated!

Rita Palazzi
Senior Engineer / Global Trade Services
FedEx Express

I keep recording this macro, but the problem I run into is that the active sheet is always the specific name of the sheet. I need a general name so that the macro will work on any given sheet.

On the sheet I am viewing, I simply want to change the tab color to black using a macro.

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

Code:
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
Next
End With
End If
Next
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.

Hey,

I don't believe this is an excel 2007-specific question, but if it is & should be relocated to a different forum, please feel free to move or ask me to move it.

I'm writing a personal macro in excel 2007 (win xp pro) that uses a userform to change the sheet name and tab color.

The problem is that my option to change the tab color to "None" doesn't actually restore it to the standard light-blue background and blue type.

Recording a macro, excel writes the following:
With ActiveWorkbook.Sheets("Sheet1").Tab
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
But running that recorded marco (with no modification) causes the error: "Run-time error '9': Subscript out of range"

See the highlighted text in my code:
Private Sub CommandButton1_Click()
Dim cSheet As Worksheet, cBook As Workbook, a As Integer
Set cBook = ActiveWorkbook
Set cSheet = ActiveSheet
If txtSheetName = "" Then
    MsgBox "Error: Sheet name cannot be blank"
    ChgSheetName.Hide
    ChgSheetName.Show
Else
For a = 1 To cBook.Worksheets.Count
    If txtSheetName = cBook.Sheets(a).Name And a <> cSheet.Index Then
    MsgBox "Error: Name is already used in workbook"
    ChgSheetName.Hide
    ChgSheetName.Show
    End If
Next a
On Error GoTo ErrorHandler
End If
    cSheet.Select
    cSheet.Name = txtSheetName
If Bluebox = True Then
    With cSheet.Tab
        .Color = 15773696
        .TintAndShade = 0
    End With
End If
If Redbox = True Then
    With cSheet.Tab
        .Color = 255
        .TintAndShade = 0
    End With
End If
If Nonebox = True Then
' Nonebox is a checkbox in the userform to remove the tab color
    With cSheet.Tab
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
End IfChgSheetName.Hide
Exit Sub
ErrorHandler:
    MsgBox "Error: " & Err.Description
End Sub
I've reached a functional though non-ideal solution using
.Color = xlNone

Where the tab color is white with black text (what i believe was the default back in Excel 2003).

I am hoping to get this working ideally though. For reference, I've attached a picture of the userform.

Hi,
I have no knowledge of VBA so any help you give me would need to be very specific. I'm using excel 2003. I would appreciate any ideas you may have.
I have a workbook that I would like to change the tab color of each sheet based on the results of cell O32 of each sheet. Each tab is exactly the same, just the data is different. O32 has a simple formula in it. I'd like the tab to change to red if the result is a negative number. So, to sum it up, I'd like the tab color to change to red for each sheet based on the cell result of O32 in each individual sheet. Can someone provide me with code for this and explain exactly how/where to enter? O32 is a merged cell, will this be an issue? Thanks in advance for any help.