+ Reply to Thread
Results 1 to 7 of 7

Macro to select multiple worksheet tabs based on color of the tabs

  1. #1
    Registered User
    Join Date
    02-01-2012
    Location
    ny
    MS-Off Ver
    Excel 2003
    Posts
    8

    Macro to select multiple worksheet tabs based on color of the tabs

    I want to create a macro which highlights any tab in the workbook that is the same color. For instance if I have a workbook where some worksheet tabs are highlighted yellow, and some worksheet tabs are highlighted red, I want to create a macro that highlights all the worksheets in the workbook that is the same color as the worksheet tab that is currently selected. So for instance, if i select a tab is the color red and run the macro, it will highlight all the red tabs in the workbook.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Macro to select multiple worksheet tabs based on color of the tabs

    Hi drewbizz and welcome to the forum,

    Using the VBA Immediate window I find:
    debug.print ActiveSheet.Tab.Color
    returns the color of the tab of the active sheet.

    You could run through all you sheets using an index of sheets, something like
    Please Login or Register  to view this content.
    Hope this helps - I guess it does if you know some VBA.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-01-2012
    Location
    ny
    MS-Off Ver
    Excel 2003
    Posts
    8

    thx marvin

    Hi instead of highlight I meant to say the color.of the tab is red for example. So will the code that you gave me work? Will I be able to copy that code so anytime I run the macro, all the worksheet tabs that are the same color as the current sheet will be selected?

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Macro to select multiple worksheet tabs based on color of the tabs

    Hi drewbizz,

    I wish it were so easy. BUT be careful what you wish for.
    I just recorded a macro that selected 3 sheets and the line of code looks like:
    Please Login or Register  to view this content.
    The above is what you are going to want in the remarked out line I wrote above.

    I really don't want to write this code for you as it is too dangerous. The reason is - If you select multiple sheets and type something into one of them it will do the same thing to all the selected sheets. This will destroy or overwrite stuff that you may not want to change on the sheets you can't see.


    Time to learn some VBA?

  5. #5
    Registered User
    Join Date
    02-01-2012
    Location
    ny
    MS-Off Ver
    Excel 2003
    Posts
    8

    reply

    Hi Marvin I understand that it is dangerous but the reason why I asked for the code is so I can change formulas on multiple sheets at once. This would save me time.thx for ur help

  6. #6
    Registered User
    Join Date
    02-01-2012
    Location
    ny
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro to select multiple worksheet tabs based on color of the tabs

    HI can anyone please help me in creating this macro? It would be a huge time saver for me.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Macro to select multiple worksheet tabs based on color of the tabs

    I'd suggest you manually Ctrl-Click on the worksheet tabs and from there change your formulas. After you do this, be sure you click on a single worksheet tab that will keep you from forgetting you have multiple sheets selected so you don't overwrite stuff you can't see.

  8. #8
    Registered User
    Join Date
    02-01-2012
    Location
    ny
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro to select multiple worksheet tabs based on color of the tabs

    Hi Marvin I currently use the control click method but I have a lot of sheets to work with so i was looking for a more efficient way. Thanks for your help so far. I just felt that there is a macro that can be done to automate it. I just don't know what the Vba code is for it. A lot of people I work with thought this macro would be a timesaver as well. I could easily tell if I messed something up.

+ 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