+ Reply to Thread
Results 1 to 9 of 9

Tab color change based on cell content

  1. #1
    Registered User
    Join Date
    11-01-2011
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Tab color change based on cell content

    Hi,
    I have no knowledge of VBA so any help you give me would need to be very specific. I'm using excel 2003. I would appreciate any ideas you may have.
    I have a workbook that I would like to change the tab color of each sheet based on the results of cell O32 of each sheet. Each tab is exactly the same, just the data is different. O32 has a simple formula in it. I'd like the tab to change to red if the result is a negative number. So, to sum it up, I'd like the tab color to change to red for each sheet based on the cell result of O32 in each individual sheet. Can someone provide me with code for this and explain exactly how/where to enter? O32 is a merged cell, will this be an issue? Thanks in advance for any help.

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Tab color change based on cell content

    Welcome to the forum!

    In VBE, Insert, Module, and paste:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-01-2011
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Tab color change based on cell content

    Hi, Thanks for the reply. I copied the code and tried it and it didn't work. I'm sure I'm doing something wrong...can you be a little more specific. When I open VBA the two drop downs say: The Middle left says General and the one on the right says NegRed. Is this correct? My macros are enabled. Thanks,

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Tab color change based on cell content

    Insert is a Menu. Module is an item in the Menu.

    or in more detail,
    Open the Visual Basic Editor by Alt+11.
    Click the Insert menu and select Module.
    Paste the code into the Module.

    You may want to review some basic tutorials:
    http://www.datapigtechnologies.com/ExcelMain.htm
    http://www.excel-vba.com/excel-vba-s...-beginners.htm
    Last edited by Kenneth Hobson; 11-01-2011 at 09:35 PM.

  5. #5
    Registered User
    Join Date
    11-01-2011
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Tab color change based on cell content

    Yes, what you have explained is what I did. It isn't changing any tabs red. Do you have any other ideas as to why it may not be working?

  6. #6
    Registered User
    Join Date
    11-01-2011
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Tab color change based on cell content

    By the way, after I entered the code and have saved and closed the workbook, when I reopen the workbood and I hit Alt + F11 to view code, a blank VBA form comes up. I have to go to the left side window and click the module folder, then module 1 for the code to populate. Is this correct? Shouldn't it show me the code when I "view code?"

  7. #7
    Registered User
    Join Date
    10-02-2011
    Location
    Los Angeles, USA
    MS-Off Ver
    2010 Home Version (doesn't include Access and Outlook )
    Posts
    22

    Re: Tab color change based on cell content

    Quote Originally Posted by sammi8796 View Post
    By the way, after I entered the code and have saved and closed the workbook, when I reopen the workbood and I hit Alt + F11 to view code, a blank VBA form comes up. I have to go to the left side window and click the module folder, then module 1 for the code to populate. Is this correct? Shouldn't it show me the code when I "view code?"
    Double clicking on the module should show the code. I suspect the code isn't in the module. Try the above instructions again to create the code.

    Alternatively, try clicking on the Developer Tab, in the Developer Area, click on Macros. A dropdown list of available macros should appear, double click on NegRed (assuming it is there).

  8. #8
    Registered User
    Join Date
    11-01-2011
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Tab color change based on cell content

    The code is in the module folder under module1. When I double click it, it is there. I was just saying that when I hit alt + F11 it doesn't show the code unless I go to it by double clicking module 1. I thought that is odd. Where is the developer tab? Thanks,

  9. #9
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    1,990

    Re: Tab color change based on cell content

    hi sammi8796
    see the attachement, the Kenneth's code works very well!
    (the developer tab is in the main meniu... see Home, Insert, Page Layout...Developer)
    Attached Files Attached Files
    Last edited by john55; 11-02-2011 at 02:20 AM.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

+ 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