+ Reply to Thread
Results 1 to 8 of 8

Colouring Tabs Based on a Cell Value

  1. #1
    Registered User
    Join Date
    07-14-2009
    Location
    Brighton, England
    MS-Off Ver
    Excel 97
    Posts
    49

    Colouring Tabs Based on a Cell Value

    Hey there, I've been trying to get a macro together which would colour all the tabs in my workbook based on whether or not a certain value exists in a certain cell. In this case the cell is C7 and the word there is "elective class:".

    At the moment this is what I have come up with:

    Please Login or Register  to view this content.
    However it doesn't work, it colours everything as though "elective class:" exists on none of the sheets. How can I correct this?

    Also I am actually going to go further with this code and will want all the sheets of one colour next to each other and all the sheets of the other colour next to each other. Excluding however the first 6 sheets which should be ignored by the macro. If anyone can help with this at the same time it would be appreciated as I have no clue how to do this part.

    Many thanks,

    Tom

    P.S. Personally if I remember correctly I think it might be something to do with using the mySht.Range("C7").Value part of the macro. I seem to remember having a similar problem with text before. I tried changing the word Value to the word Text but to no avail.
    Last edited by TBrooker; 07-28-2009 at 07:45 AM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Colouring Tabs Based on a Cell Value

    UCase is upper case so it will never find lower case characters! Change the first character as below:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-14-2009
    Location
    Brighton, England
    MS-Off Ver
    Excel 97
    Posts
    49

    Re: Colouring Tabs Based on a Cell Value

    Ah, for shame.

    Thanks for that.


    How can I get it to sort only the 7th worksheet onwards by the way?
    Last edited by TBrooker; 07-28-2009 at 07:48 AM.

  4. #4
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Post Re: Colouring Tabs Based on a Cell Value

    One way might be with a counter:

    Please Login or Register  to view this content.
    or you could access your sheets via the index instead of 'For Each...'

    Please Login or Register  to view this content.
    The second method should be quicker, and certainly would be if you are starting at a higher numbered sheet, as it will not cycle through all the sheets that you AREN'T interested in, whereas the first method would, (whilst incrementing it's counter)
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

  5. #5
    Registered User
    Join Date
    07-14-2009
    Location
    Brighton, England
    MS-Off Ver
    Excel 97
    Posts
    49

    Re: Colouring Tabs Based on a Cell Value

    Phil, cheers as always.

    Getting an error now though.

    "Compile error: Invalid Next control variable reference"

    The mySht statement is then highlighted at the end.

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Colouring Tabs Based on a Cell Value

    Change the Next line to
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-14-2009
    Location
    Brighton, England
    MS-Off Ver
    Excel 97
    Posts
    49

    Re: Colouring Tabs Based on a Cell Value

    Cheers folks!

  8. #8
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: Colouring Tabs Based on a Cell Value

    Ooops, sorry about that. My bad as I didn't even look, I normally just use "Next" so it wouldn't have been a problem :P

+ 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