+ Reply to Thread
Results 1 to 6 of 6

Changing tb color and text

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Changing tb color and text

    I've tried using VB in Excel based on some posts that I have read, but I'm sill having zero luck! What I'm trying to do is a two step process.

    1) Getting the Tab name to change based on a name in a given cell.
    i.e. - If cell A1 is Dan, change the Tab to say Dan. (There isnot a set list of names, it could be any name possible)

    2) Change the tab color if cell C74 is:
    Cell Value >=80, change to Green
    Cell Value between 60 and 80, change to Yellow
    Cell Value <=60, change to Red

    Again, I've never really used VB, so if someone could give me the formula so I can cut and paste it into VB, that would be awesome! Also, once I paste it into the VB box, what is the next step to get it to work.
    Thanks for any assistance in advance!

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Changing tb color and text

    try the macro. in the VIEW tab in the workbook select MACROS (to the right)
    Select view macros
    choose macro named novice (the only one!)
    select edit
    and there you have it
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-06-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Changing tb color and text

    Thanks RCM, I think you are on to something, but I can't get it to work just right, I thought I'd be able to figure it out based on the code you gave me. If you could look at the attached, this is what I'm trying to do.
    1) The Value of A2 becomes the tab name (ADAMRE)
    2) Depending on the score in cell C74, the tab color will be either green, yellow or red.
    Vendor Tab Test.xlsx

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Changing tb color and text

    I wrote the macro to do what you wanted
    Yet, I would suggest to add these two lines to the code you have for cell(C74)

    If Cells(1, 2) <> "" Then Sheets(i).Name = Cells(1, 2)
    ActiveSheet.Tab.color = Cells(74, 3).Interior.color
    Attached Files Attached Files
    Last edited by rcm; 12-07-2012 at 12:13 AM. Reason: typo

  5. #5
    Registered User
    Join Date
    12-06-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Changing tb color and text

    That looks great, thank you. However, when I go to test it by changing the name or values, nothing happens. Is there an Add-In that I need to activate or something that I need to select to make the macros active?

  6. #6
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Changing tb color and text

    Macros need to be manually executed, so after you fill the form you have to manually execute the macro. To run the macro step by step you could go
    VIEW->MACRO->VIEW MACRO (Select Macro)-> EDIT, select a line at the top (click the left margin of a line of code) (a bourbond circle will apper), run the macro and it will stop in the chosen line. F8 will execute it step by step.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1