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?
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?
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.
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).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.
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=486959
>
>
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).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.
> >
> >
> > --
> > MrShorty
> > ------------------------------------------------------------------------
> > MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> > View this thread: http://www.excelforum.com/showthread...hreadid=486959
> >
> >
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" <[email protected]> wrote in message
news:[email protected]...
> 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).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.
>> >
>> >
>> > --
>> > MrShorty
>> > ------------------------------------------------------------------------
>> > MrShorty's Profile:
>> > http://www.excelforum.com/member.php...o&userid=22181
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=486959
>> >
>> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks