Possible? If total in a worksheet > 0, highlight worksheet tab?

T

TKGerdie

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?
 
M

MrShorty

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).value>0 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.
 
T

TKGerdie

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.
 
T

TKGerdie

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?
 
P

Paul B

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
 
Top