+ Reply to Thread
Results 1 to 8 of 8

sheet tab color change

  1. 01-27-2020, 06:10 AM #1
    firo
    firo is offline
    Registered User
    Join Date
    01-27-2020
    Location
    bratislava
    MS-Off Ver
    2007
    Posts
    2

    sheet tab color change

    Hi , i would lie to change a sheet color - tried to use this code

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice 20160930
    If Target.Address = "$C$11" Then
    Select Case Target.Value
    Case "2016"
    Me.Tab.Color = vbWhite
    Case "2017"
    Me.Tab.Color = vbYellow
    Case "2018"
    Me.Tab.Color = vbRed
    Case "2019"
    Me.Tab.Color = vbGreen
    Case "2020"
    Me.Tab.Color = vbBlue
    Case Else



    but it works only if I manually write down a value (e.g. 2017 etc.). But my intention is to change color based on the value copied from another sheet , in this case it is ='Sumár úľov'!F11 - but it does not work. Any suggestion would be welcome
    Reply With QuoteRegister To Reply
  2. 01-27-2020, 06:15 AM #2
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,911

    Re: sheet tab color change

    Use the Worksheet_Change event in the other sheet instead, and change Me to the relevant sheet codename.
    Rory
    Reply With QuoteRegister To Reply
  3. 01-27-2020, 06:16 AM #3
    BMV
    BMV is offline
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: sheet tab color change

    You have to use Calculation event in stand of change but without target.

    Please Login or Register  to view this content.
    Last edited by BMV; 01-27-2020 at 06:25 AM.
    Reply With QuoteRegister To Reply
  4. 01-27-2020, 06:17 AM #4
    mikerickson
    mikerickson is offline
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: sheet tab color change

    If C11 holds a formula, the changing of that value won't trigger a Change event. It will trigger a Calculate event.

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.
    Reply With QuoteRegister To Reply
  5. 01-27-2020, 06:18 AM #5
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: sheet tab color change

    • Put the code in the worksheet Sumár úľov code module.
    • Change the C11 to F11
    • Change the Me to the name of sheet you want to change the tab color.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.
    Reply With QuoteRegister To Reply
  6. 01-27-2020, 07:13 AM #6
    firo
    firo is offline
    Registered User
    Join Date
    01-27-2020
    Location
    bratislava
    MS-Off Ver
    2007
    Posts
    2

    Re: sheet tab color change

    Well, I am certainly not a VBA expert, just an ordinary Excell user, who tries to make his worksheets nicer and more effective - Most probably i would not be able to find such an "easy" solution like you proposed. It works as It should - thank you very much. Would there also be a way how to apply this code for multiply sheets, without putting it into each sheet again? 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 01-27-2020, 07:15 AM #7
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: sheet tab color change

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I did it for you this time. Please read forum rules. Thanks
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  8. 01-27-2020, 07:16 AM #8
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: sheet tab color change

    Administrative Note:
    Please add the code tags yourself to your original post. Due to a forum glitch I cannot edit your post to add the missing tags
    Thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup values on another sheet and change background color on current sheet
    By x65140 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2019, 08:15 AM
  2. 2 cell condition sheet/tab color change, and VBA copying with sheet
    By ohnoitsjoe89 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-08-2014, 01:01 AM
  3. Allow font color change and such - protected sheet
    By jw01 in forum Excel General
    Replies: 1
    Last Post: 04-13-2013, 10:27 AM
  4. [SOLVED] change the sheet tab color
    By Tracy in forum Excel General
    Replies: 1
    Last Post: 06-08-2006, 01:35 PM
  5. How can I change sheet tab color based on cell value in sheet?
    By SCAScot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2005, 05:50 PM
  6. change sheet tab color?
    By mark in forum Excel General
    Replies: 2
    Last Post: 05-26-2005, 06:23 PM
  7. [SOLVED] How do I change the font color of my sheet tabs?
    By DungeonsOfAlcatraz in forum Excel General
    Replies: 1
    Last Post: 01-03-2005, 06:06 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1