Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible? If total in a worksheet 0, highlight worksheet tab?
I have multiple worksheets in this workbook. Each worksheet is a different
carrier's pricing. What I want to do is, if the total cell in the worksheet is greater than 0, than make the tab a different color or something so that it stands out. Is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible? If total in a worksheet 0, highlight worksheet tab?
Still looking for help? Can anyone tell me if this is possible?
"TKGerdie" wrote: I have multiple worksheets in this workbook. Each worksheet is a different carrier's pricing. What I want to do is, if the total cell in the worksheet is greater than 0, than make the tab a different color or something so that it stands out. Is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible? If total in a worksheet 0, highlight worksheet tab?
Yes, it is possible. You haven't given very many specific needs for you procedure, but the basic VBA statement for what you want to do would be: If worksheets(1).cells(1,1).value0 then worksheets(1).tab.colorindex=24 Change the worksheets() index to what you need, the cells reference to the cell with the total in it, and the colorindex value to whatever color you want. This statement could be placed inside a loop that loops through any/all worksheets in the workbook. The procedure could be associated with a button that you click to initiate the procedure, or it could be placed in an event procedure so it runs automatically. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=486959 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible? If total in a worksheet 0, highlight worksheet ta
I'm very, very new to VBA - just using the Microsoft on line training to
begin using it, in fact. Is there somewhere you would recommend I look to better understand how to do this? The online training only speaks to using Modules to have the code work when you run it yourself. "MrShorty" wrote: Yes, it is possible. You haven't given very many specific needs for you procedure, but the basic VBA statement for what you want to do would be: If worksheets(1).cells(1,1).value0 then worksheets(1).tab.colorindex=24 Change the worksheets() index to what you need, the cells reference to the cell with the total in it, and the colorindex value to whatever color you want. This statement could be placed inside a loop that loops through any/all worksheets in the workbook. The procedure could be associated with a button that you click to initiate the procedure, or it could be placed in an event procedure so it runs automatically. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=486959 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible? If total in a worksheet 0, highlight worksheet ta
The idea I am trying to accomplish is this: The main worksheet is the main
invoice sheet. All the other worksheets are price sheets for each contract we have. Each price sheet has a cell at the bottom with the total amount on that sheet. What I would like to do is if the people enter a total into that individual price sheet, then the tab would be highlighted in a different color. Then our Finance person would be able to tell very easily which tab has the pricing in it. Does this help? "TKGerdie" wrote: I'm very, very new to VBA - just using the Microsoft on line training to begin using it, in fact. Is there somewhere you would recommend I look to better understand how to do this? The online training only speaks to using Modules to have the code work when you run it yourself. "MrShorty" wrote: Yes, it is possible. You haven't given very many specific needs for you procedure, but the basic VBA statement for what you want to do would be: If worksheets(1).cells(1,1).value0 then worksheets(1).tab.colorindex=24 Change the worksheets() index to what you need, the cells reference to the cell with the total in it, and the colorindex value to whatever color you want. This statement could be placed inside a loop that loops through any/all worksheets in the workbook. The procedure could be associated with a button that you click to initiate the procedure, or it could be placed in an event procedure so it runs automatically. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=486959 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible? If total in a worksheet 0, highlight worksheet ta
Tk, try this,
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'Change to you cell If IsNumeric(Range("A1")) And Range("A1") 0 Then '3 will trun the tab red ActiveSheet.Tab.ColorIndex = 3 Else 'will set the color to none if not 0 ActiveSheet.Tab.ColorIndex = xlNone End If End Sub To put in this macro, from your workbook right-click the workbook's icon and pick View Code. This icon is to the left of the "File" menu this will open the VBA editor, in Project Explorer double click on thisworkbook, under your workbook name, if you don't see it press CTRL + r to open the Project Explorer, then, then paste the code in the window that opens on the right hand side, press Alt and Q to close this window and go back to your workbook. If you are using excel 2000 or newer you may have to change the macro security settings to get the macro to run. To change the security settings go to tools, macro, security, security level and set it to medium -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "TKGerdie" wrote in message ... The idea I am trying to accomplish is this: The main worksheet is the main invoice sheet. All the other worksheets are price sheets for each contract we have. Each price sheet has a cell at the bottom with the total amount on that sheet. What I would like to do is if the people enter a total into that individual price sheet, then the tab would be highlighted in a different color. Then our Finance person would be able to tell very easily which tab has the pricing in it. Does this help? "TKGerdie" wrote: I'm very, very new to VBA - just using the Microsoft on line training to begin using it, in fact. Is there somewhere you would recommend I look to better understand how to do this? The online training only speaks to using Modules to have the code work when you run it yourself. "MrShorty" wrote: Yes, it is possible. You haven't given very many specific needs for you procedure, but the basic VBA statement for what you want to do would be: If worksheets(1).cells(1,1).value0 then worksheets(1).tab.colorindex=24 Change the worksheets() index to what you need, the cells reference to the cell with the total in it, and the colorindex value to whatever color you want. This statement could be placed inside a loop that loops through any/all worksheets in the workbook. The procedure could be associated with a button that you click to initiate the procedure, or it could be placed in an event procedure so it runs automatically. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=486959 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to search for and display data in another worksheet | Excel Worksheet Functions | |||
grand total column B from every worksheet in workbook | Excel Discussion (Misc queries) | |||
Total remaining formula | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Worksheet name and Backward compatibility | Excel Discussion (Misc queries) |