Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Code To Color Excel Sheet Tabs


I need a macro code to color 31 excel sheet tabs with shade cyan with a click.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Count Sheet Tabs Based On Color
I have a worksheet with like lots of color coded tabs and the tabs increase everyday. The tabs are categorized based on 6 colors. I created a first tab in order to give how many tabs i have another each color. Is that doable in excel. Do I need a macro for this.

All I want is each time i open the excel sheet the first tab will be automatically updated with how many tabs I have under each color instead of me counting the tabs.

View Replies!   View Related
Rename Tabs With Code
I am pulling my hair out in frustration with this right now. I have a .csv file that I am attempting to run a macro on. I will need to move between tabs, and want to rename a tab to remove the date as it is not consistent in format. I have tried numerous ways, and I don't get an error message, but the tab is not renamed either. Code I have tried is below ....

View Replies!   View Related
Code To Select All Tabs In A Workbook
I have been recording different sets of commands then copying/pasteing them together to complete what I want. I have tried to clean it up as best I can but im sure it does things it does not need to. Is there a way to tell it to select all sheets in "Book2.xls" then remove the formatting (font color,comments,shading) rather than using the Array and naming each and every sheet?

What this will end up doing is openeing about 12 files, copying specific tabs from each of those files and pasteing them all to the same workbook (in this example it would be Book2). Then removing the formatting.

Workbooks.Open Filename:="F:123Book2.xls"
Windows("Book1.xls").Activate
Cells.Select
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy Before:=Workbooks("Book2.xls").Sheets(1)
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
Selection.ClearComments

View Replies!   View Related
VBA Code To Renames Tabs Dependent On Cell Name
Can anyone please help me with some code to rename tabs to match a value in a cell.

When I receive the spreadsheet it is in the standard Sheet1, Sheet2 and so on.

Now in cell E2 there is a Budget Cost code. Now I have to manually rename the tabs to match this code and it is quite time consuming.

Also Sheet1 is different as it has a logo in cell E2 so the budget code on that sheet is in E3 but like I said earlier Sheet2, Sheet3 and so on the budget code is in E2.

Can anyone post a little macro that will rename all the tabs on the worksheet to match the budget code in the above mentioned cells.

View Replies!   View Related
Rearrange The Order Tabs Display In Through VBA Code?
Is there any way to rearrange the order tabs display in through VBA Code?

View Replies!   View Related
VBA Code To Activate Second-to-last And Last Worksheet Tabs In A Workbook?
I use a workbook where new worksheet tabs are added and removed daily. Every day I use the second-to-last tab (2nd from the right) and the last tab (far right). What code would enable me to activate and reference both of these tabs individually? I believe it would be something like the code below but I can't figure it out...

View Replies!   View Related
Apply Macro Code To All Tabs / Sheets
I havae the following macro which i recorded in Excel. I want this Macro to run after another macro that groups data and creats tabs. The following macro will then add a column and run an array formula. I think this can be done in a loop but i'm not sure how to do it. This is working but takes a long time and times out by the time it reaches the last tab.

Application.Run "TotalHrs"
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("I4").Select
ActiveCell.FormulaR1C1 = "Invoiced Amount"
Range("I5").Select
Columns("I:I").EntireColumn.AutoFit
Selection.FormulaArray = _
"=INDEX(VLookup!R2C2:R242C4,MATCH(RC[-3]&RC[-2],R5C6:R2500C6&R5C7:R2500C7,0),3)*R[3]C[-1]"
Selection.Copy
Range("I6:I1000").Select...................

View Replies!   View Related
Sum Across Sheet Tabs
Is it possible to Sum across sheet tabs, if so how?

View Replies!   View Related
Sheet Tabs Count
I know excel has a built in function for this. But it won't do what I need for this below.

Say, I have 9 worksheets open. In cell N7 on sheet 1 I need it to count the worksheet tabs.
But the dilema is to only count the tabs that will equal sheet 1, sheet 2, sheet 3, sheet 4, sheet 5, Notice, I have a space before the number. Other sheets have a specific names to them which I do not want to count.

So if sheets 1 thru 3 are visible with the other named sheets visible cell N7 will count 3


View Replies!   View Related
Sheet Tabs In 2007
Have designed a spreadsheet in 2003 at work and got home to work on in it using my machine which has 2007 loaded.

Have hidden the sheet tabs on the 2003 sheet and can not find the option in 2007 to make them visible again.

I realise I can ctrl-pgup and pgdn but I got a fair bit of movement from sheet to sheet to do and that wont be practical.

View Replies!   View Related
Locking Sheet Tabs
Is there a way I can lock the sheet tabs so they cannot be viewed or turned on by anyone except me?

View Replies!   View Related
Excel Sheet Tabs On The Top Row
is there a way by which i can have Excel sheet tabs on the top rather than currently at below.

View Replies!   View Related
Referencing Sheet Tabs
Is there a way to use the "value" of a name in a drop-down list to reference a sheet tab name in a formula?

A1 has a drop down list. When a name (Bob's Sales) is selected from the drop down list, B1 shows the value of cell D5 from sheet "Bob's Sales".

View Replies!   View Related
Alphabetize Sheet Tabs
I've got a bunch of worksheets that I'd like to re-arrange in alphabetical order. Anyway to do this quickly? Or do I just have to drag and drop them myself? (WXP, Excel XP).

View Replies!   View Related
Hiding Sheet Tabs
Hiding sheet tabs. can you hide tabs on excel?

View Replies!   View Related
Lock Sheet Tabs Using VBA
I need to get rid of the option to DELETE a sheet. However, when I password protect the WORKBOOK (which does this fine) none of the Macro buttons will execute, even with Macro security settings set to ENABLE ALL.

Looks like I will need some VBA code to not allow any of the sheets in the workbook to be deleted.

View Replies!   View Related
2000 Sheet Tabs Missing
I am pulling my hair out trying to figure this out. Any help at all is greatly appreciated. I created a spreadsheet for a friend to track his profit/loss for his business; he is not very computer literate so I created separate tabs for each month. I have no idea what he did but the horizontal scroll bar and all of the tabs are not visible, he is using excel 2000 on windows XP. Unfortunately we are not in the same area so I canít go look at his screen. When he emailed the file back to me and I opened it, it looks fine. Also when he opens any other file it does the same thing. I have gone through all of the obvious causes, everything is correct in the tools, options.

View Replies!   View Related
Automatically Rename Sheet Tabs
I have a workbook with multiple tabs. On sheet one a person chooses a date to initialise a formula. That date is then copied to sheet two and placed in cell D5. The date is projected out for two weeks and then the next fornight starts on sheet three and so on. The start date for each new fortnight appears in cell D5 of each sheet. I want that information to be the name of the tab for sheet two, three etc. I have put this code into the "This Workbook" section:

Private Sub Worksheet_Change(ByVal Target As Range)

' Renames all worksheet tabs with each worksheet's cell A1 contents.
'If cell A1 has no content, then that tab is not renamed.

For i = 1 To Sheets.Count
If Worksheets(i).Range("$D$4").Value "" Then
Sheets(i).Name = Worksheets(i).Range("$D$4").Value
End If
Next
End Sub

I have also tried:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$4" Then ActiveSheet.Name = Left(Target.Value, 10)
End Sub

I then go back into sheet one and change the date and nothing happens. I seem to be missing something. Can anyone please help?

View Replies!   View Related
Sheet Tabs Not Visible In 1 Workbook
I know this is probably an easy one, but can anyone help, I have a spreadsheet with aboout 8 worksheet tabs, for some reason they have disappeared I have emailed the spreadsheet to my work email and everything is fine, does anyone out there know what I have done. I have checked the tools options and view tab as informed and the sheet tabs are definately checked, also I have ensured that the tabs are not hidden.

View Replies!   View Related
Consolidate All Of The Tabs In The Master Sheet
I have a file that will have a maximum of about 25 tabs in it. They are all the same format - Column A have account codes, Column B have the account name and Column C through to Column AD have figures. Column B has a drop down menu, so not all tabs will have the same account code on the same row.

I thought that I would create another spreadsheet in the file and call it a master sheet with the same formats as all the other tabs but this master file will have all of the account codes available and will not have a drop down menu in column B. What I wanted to do was consolidate all of the 25 tabs in the master sheet.

Example Worksheets
TAB 1
Acc Code Desc Amount
1________a___10
2________b___10

TAB 2
Acc Code Desc Amount
1________a____10
2________b____10...........


View Replies!   View Related
Delete All Sheet Tabs Apart Form The First 3
Just created 1800 sheets by not closing a loop, just wondering if anyone has a quick solution to delete all of these apart from the first three.

View Replies!   View Related
Copy Worksheet To End Or Far Right Of Sheet Tabs
This macro helps me create a blank worksheet and automatically names it from the list in column A. Unfortunately, it adds the worksheets to the left of my 'master' sheet called "Tab Names". When I try adding "after" when it adds a new sheet, I keep getting a "compile error: Expected: end of statement". I am positive you will know what steps I'm missing.

' Vendor List Subroutine to
' Open New Worksheet called Tab Names worksheet
' With data from Tab Names
' Rename sheet to name from list and start again
' For all names in list
Sub NewTabsFromList()
Dim cCell As Object, i As Integer 'Two variables cCell (current Cell) object and i (standard counting integer)
Cells(1, "A").Select 'Ensure that we start each time at the top of the worksheet
Application. ScreenUpdating = False 'Turn of screen updates whilst macro is running
For Each cCell In Range(Cells(1, "A"), Cells(1, "A").End(xlDown)) 'Will run our code through each cell with text
Set NewSheet = Sheets.Add(Type:=xlWorksheet) 'Add new worksheet
NewSheet.Name = "Tab Names Worksheet" 'rename worksheet
Sheets("Tab Names worksheet").Name = cCell.Value
Next cCell
End Sub

View Replies!   View Related
Unhide Sheet Tabs After DisplayFullScreen = TRUE
I want to restate the tabs after using DisplayFullScreen = true
and setting the commandBars ("").Visible = false

I would be very thankful for your Help as I'm newie to VBA in Excel.


View Replies!   View Related
Merge Data From Many Many Tabs (worksheets) Into A New Sheet
Basically I inherited a Excel Workbook which has 128 different tabs, unsurprising this is almost impossible to manage.

My requirement is to merge all of the 128 tabs into 1 tab 'Sheet1'.
The macro should keep going even if there are blanks, it should move onto the next tab only when there are over 10 consecutive blanks in Column B.

I have tried searching the forum but nothing seems to cater for the specifics of this case. I would really appreciate some help. I mean really, i am in a mad panic thinking i will have to do 128 tabs manually otherwise.

View Replies!   View Related
Copy Rows From Multiple Tabs Into One Sheet
I am looking to write a macro that will take 5 sheets and paste the rows into 1 summary tab. The names of the sheets are, CMH, ORD, JFK, LAX, and MIA. There are other sheets in the book but I donít want any information from them. The five sheets have the same columns. I want to paste only the rows of the last entry for Origin and Forwarder. I have enclosed an example. So in rows 2 & 3 we have the same Origin-Forwarder combo but I only want the most current which would be row 3. Some Origin-Forwarder just has one entry so of course I would want that one.

View Replies!   View Related
Activate Sheet And Disable Tabs On Open
I am trying to create an event macro that would deactivate column and row headers when the workbook is launched and activate a sheet. i am not managing to do this,

View Replies!   View Related
Concatenate Function ... Same Spread Sheet Different Tabs
I want to concatenate two columns in a separate tab but when I do, the values appear as ....

View Replies!   View Related
Sheet Tabs Disappear When I Open Excel
my sheet tabs sometimes disappears when I open excel. I know you can go through the start button and the advanced option to display the sheet tabs. Is there code I can incorporate so that on the opening of the excel file after the splash screen runs its course, the sheet tabs are displayed.

View Replies!   View Related
Macro - Rename Only & Sheet... & Tabs With Last 8 Characters From Cell
I pull an excel sheet from a database each day that gives me sales data for different products sold. The way the spreadsheet comes from the system, the first tab is named "Document Map" and lists all of the products, and each product then has its own tab but they're generically named (Sheet1, Sheet2, etc.). The name of the product is always in cell T8, but the name itself is only the last 8 characters of that cell.

What I'm hoping to accomplish with a macro is two things:

1) Retrieve the last 8 characters from T8 in each tab, and name the tab with those characters (same effect as the formula "=right(T8,8)").
2) Ensure that this only occurs on tabs with the word "Sheet" in the beginning. Depending on the day, there could be any number of products and they each get their own tab, so I was hoping to be able to get the macro to only work on sheets where the first 5 characters are "Sheet" regardless of the number after that.

View Replies!   View Related
Move The Sheet Tabs From The Bottom Of The Screen To The Side?
Is there a way to move the sheet tabs from the bottom of the screen to the side?

View Replies!   View Related
Macro To Load All Files Within A Folder To New Tabs With File Name As Sheet Name
I wish for a macro to look in to a directory which remains constant, but then looks within a folder which is specified via a cell value in order to load a specific sheet from each file in to the workbook I am currently using. I wish for the loading to be done without having to open the workbooks manually, so something which opens them copies the data from the sheet specified and then closes.

This data is then to be pasted in to a new sheets within my workbook which are named after the file names that it pulls the "dump" sheet from.

As a side note, I will be using these sheets to produce calculations hopefully automatically as soon as they are loaded. They all have the same "shape" but contain differing data. Will it be easier in the long run for me to use this method of pasting data to new sheets named as their file names assuming there will be around 25 "dump" sheets needing to be loaded or would i be better having them paste all to one sheet just underneath each other with a couple of rows separating them?

View Replies!   View Related
Click Button To Rename Sheet Tabs Based On Range
I searched a few times for the answer to my problem, using different search terms, but I've come up somewhat empty (this *might* be because I'm not up-to- speed with Excel's terminology?). Anyway, my problem is this: I have a workbook which is designed to track various data for a corporate training course ( sheets for attendance, grades, tool logins, etc). After all of these are sheets numbered 1-25 (we never have more than 25 students in a class, but usually around 20). The numbered sheets correspond to the student's number in the on the class list sheet (so, the first student would have personal "report card" information on sheet 1; the eleventh student in the class list would have their information on sheet 11, etc).

What I would like to do is have some way of putting a button on the class list sheet which would look at student list and rename the sheets to correspond to the student names. For example, if the fifth student listed was 'John Doe', it would rename his personal sheet ('5') to 'John Doe'. Obviously, I would need to do it for all of the students, and IDEALLY it would then get rid of any numbered sheets which are not used (having 21 students would only rename the first 21 sheets, but then hide sheets 22, 23, 24, and 25).

View Replies!   View Related
Use The Names Of The Tabs In The Summary Page And Create It Into A Formula To Lookup Fixed Cells Within The Various Tabs
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.

View Replies!   View Related
Referencing Selected Tabs To Print Selection & Update Table Of Contents Sheet
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:

1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case

2. the ToC has to change depending on the sheets selected.

I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.

What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).


View Replies!   View Related
Using Code To Set Color
I use the code below to automatically set a black dot in cell
A11. I would like to add code which would color that dot
the same color as a manually placed checkmark in column C11 which
is symbol ť.

In other words, in addition to the code printed below, I would
like to color the dot the same color as the color of the arrow
symbol ť in column C11.

Is that possible? Thanks

=IF(AND(B11="Ł",D11="X"),CHAR(108)," ")

View Replies!   View Related
Color Code Tweak
I am using the following code to change colors in set of autoshapes (Thanks Colin_L and Norie) and it works fine for the first autoshape but has it has no effect on the subsequent autoshapes. I plan on using the code for about 200 autoshapes, I thought I could just copy the first code and keep adding it on for each autoshape... I am guessing that the code is stopping after the first set and not continuing?


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$46" Then

'Change autoshape color to red depending upon cell value, or blank of no value is entered.
With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
If Target.Value = "" Then
.SchemeColor = 1
ElseIf Target.Value >= 422 Then
.SchemeColor = 50
ElseIf Target.Value >= 1 Then
.SchemeColor = 10
Else
'it must be less than 1
End If
End With
End If......


View Replies!   View Related
VB Border Color Code
part of my code looks like this. Instead of it being if c.interior.colorindex i want it to be if top border color = 4.

How do i syntax this?

For Each c In Worksheets("Sheet1").Range("B7:DE7").Cells
If c.Interior.ColorIndex = 4 Then Range("DH7").Value = Range("DH7").Value + c.Value
Also, is there a way to specify Range("DH") and make the row be the current row, not hard coded to 7


View Replies!   View Related
Code Color Cells
I have a code that updates data from one file to another.....esentially, i run this code daily and it updates to a compiled daily log of all of the previous days data.

Currently, i am manually coloring the cells to visually assist in grouping what data has been uploaded for each day.

Sub UpdatePOQntyReducDB()
Dim lookupfilename As String

View Replies!   View Related
Text Color Code
Is it possible to have a code that says if I have a range of cells....say a1:d1 with d1 having a date in it....have that code say that if that date in d1 is greater than 30 days from today, make that whole range of cells (a1:d1) change color like to red.

View Replies!   View Related
Color Code After Adding
I was wondering if there's a way to make the numbers in a column over 50 (as an example) green and numbers less than 50 red. (The numbers in this column are the sums of other cells, but I guess that doesn't really matter.)

This way when there's a lot of things going on you can see what's what without filtering or rearranging.

View Replies!   View Related
Passing Variables And Values Between Sheet Code And Module Code
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure: ...

View Replies!   View Related
Color Code Mistakes From Macro
I am trying to color code mistakes I find when running formulas in a macro. Right now I have it set up to color code mistakes red using the following code after the formula has been inserted in column E.

Range("E2:E75").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Check"""
Selection.FormatConditions(1).Interior.ColorIndex = 3

My issue is, some of my checks have more than one variable as a wrong answer. For example, one check may include Check and Bad as possible outcomes of the formula in the cell.

the code required to allow two different variables to cause the cells to change color.

I tried a second set of the code for the same column and changed the formulas word to Bad but it did not highlight the cells that came out with Bad as the outcome of the formula.

View Replies!   View Related
Color Different Peoples Source Code
I am wondering if there is any good way of coloring different sections of the source code in excel to show what different programmers have added?


View Replies!   View Related
Color Code Cells Appearing More Than Once
I must highlight cells that appear more than once.
What I mean is that if an item in a cell appears more than once I highlight it in blue, if the same happens twice I highlight that cell red etc and so one. does anyone know how to do this in excel.


View Replies!   View Related
VBA Code - Color Changing Cells
I am trying to create a model that has a mixture of conditional formatting and data validation formulas within it, but have run into a problem in one area. Essentially, cell E10 is a Validation which pulls a drop down list of names. One of the options in this list is "Other".

I want to create a VBA code to make sure that cell E12 will get rid of any input, turn grey (color index = 15), and potentially lock (if possible!) if the "Other" is chosen by the user in cell E10. If any other value is chosen for cell E10, Cell E12 needs to remain unlocked and white (color index=2).


View Replies!   View Related
Color Cell - Code Not Working
I am trying to a macro that allows me to change the backgroud color and font color depending of the value of the cell.

For example is cell A1 is having the value between 1 to 3 the background color of the cell will the light turqoise, if the value is between 4 to 20 the cell background color will be green and so on.

I have based on certain posted example and adapt to my code but somehow it is not working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Value
Case Is = 1, 2, 3
Target.Interior.ColorIndex = 34
Case Is = 4, 5, 10, 20
Target.Interior.ColorIndex = 43
Case Is = 30, 40, 50

View Replies!   View Related
Vba Code To Change The Cell Color ..
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.

View Replies!   View Related
Color Code Values With Function
I have values that I want to color code in my table. Originally I was tagging them as numbers, but instead I need them as colors.

Function conversion(pVal As Double)

If pVal > 0.05 Then
conversion = "1"
ElseIf pVal > 0.001 Then
conversion = "2"
ElseIf pVal > 0.0001 Then
conversion = "3"
ElseIf pVal > 0.00001 Then
conversion = "4"

End If

End Function

Now I want instead of conversion = "1" etc to be

Function conversion(pVal As Double)

If pVal > 0.05 Then
(make white)
ElseIf pVal > 0.001 Then
(make brighter blue)
ElseIf pVal > 0.0001 Then
(make green)
ElseIf pVal > 0.00001 Then
(make navy blue)

End If

End Function

I want the text and cell the same color (number and cell so the number disappears so that I just have a colored cell but the value is still there if I want to look at it).

View Replies!   View Related
Color Code Chart Columns
I am trying to color code the cubes in my chart. I'd like to be able to be able to specify a specific color per cube. What code what I write for the Sheet to do this?

View Replies!   View Related
Color Code Protected Cells
Well I am back to the colour coding of protected cells

I am using excell 2003

Many cells in the worksheet are locked

My problem is that when I am debugging I forget to turn sheet protection on

when I am finished And then users can overtype the cell formulae

How can I set excel up so that when the sheet is protected all the Locked cells have a green background , but when the protection on the worksheet is turned off
then the locked cells must be a red

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved