+ Reply to Thread
Results 1 to 4 of 4

Conditionally changing tab color with VBA

Hybrid View

dcphoto Conditionally changing tab... 09-17-2013, 07:31 PM
GC Excel Re: Conditionally changing... 09-17-2013, 10:44 PM
dcphoto Re: Conditionally changing... 09-25-2013, 12:42 PM
dcphoto Re: Conditionally changing... 09-25-2013, 01:23 PM
  1. #1
    Registered User
    Join Date
    02-04-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    5

    Conditionally changing tab color with VBA

    I've been searching the Internet for hours, and I can't find anything that fits my needs.

    I want to be able to change tab colors automatically so that the current months tab (MonthName Year) is red, past months turn black, and future months are blue.

    Everything I've found so far changes color based on a particular cell, rather than a month, and I don't want to have to update stuff manually every month.

    Thanks y'all!

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,414

    Re: Conditionally changing tab color with VBA

    Hi,
    Try this :

    Sub ColorTabs()
       Dim i As Integer, vMonth, vYear
       Dim vMonths
       
       vMonths = Array("January", "February", "March", "April", "May", _
                "June", "July", "August", "September", "October", "November", "December")
                 
       For i = 1 To Worksheets.Count
          vMonth = Split(Sheets(i).Name, " ")(0)
          vYear = Split(Sheets(i).Name, " ")(1) * 1
          vMonth = Application.Match(vMonth, vMonths, 0)
          
          If vMonth < Month(Date) And vYear <= Year(Date) Then
             Sheets(i).Tab.Color = RGB(255, 0, 0)
          Else
             Sheets(i).Tab.Color = RGB(0, 0, 255)
          End If
       Next i
    End Sub
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    02-04-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditionally changing tab color with VBA

    Thank you so much for giving me a starting place. It works but I made a change to accommodate black for the past, blue for present, and red for the future.

    Sub ColorTabs()
       Dim i As Integer, vMonth, vYear
       Dim vMonths
       
       vMonths = Array("January", "February", "March", "April", "May", _
                "June", "July", "August", "September", "October", "November", "December")
                 
       For i = 1 To Worksheets.Count
          vMonth = Split(Sheets(i).Name, " ")(0)
          vYear = Split(Sheets(i).Name, " ")(1) * 1
          vMonth = Application.Match(vMonth, vMonths, 0)
          
          If vMonth < Month(Date) And vYear <= Year(Date) Then
             Sheets(i).Tab.Color = RGB(0, 0, 0)
          ElseIf vMonth = Month(Date) And vYear = Year(Date) Then
             Sheets(i).Tab.Color = RGB (0, 0, 255)
          Else: Sheets(i).Tab.Color = RGB(255, 0, 0)
          End If
       Next i
    End Sub
    Problem is, no matter how I change things I can't get the 2012 entries to change to black. I'm not really sure where the problem is.

  4. #4
    Registered User
    Join Date
    02-04-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditionally changing tab color with VBA

    Ah ha! Got it figured out. Probably not the most elegant or efficient way, but it's doing what I want. That's for your help!

    Sub ColorTabs()
       Dim i As Integer, vMonth, vYear
       Dim vMonths
       
       vMonths = Array("January", "February", "March", "April", "May", _
                "June", "July", "August", "September", "October", "November", "December")
                 
       For i = 1 To Worksheets.Count
          vMonth = Split(Sheets(i).Name, " ")(0)
          vYear = Split(Sheets(i).Name, " ")(1) * 1
          vMonth = Application.Match(vMonth, vMonths, 0)
          
          If vMonth <> Month(Date) And vYear < Year(Date) Or _
          vMonth < Month(Date) And vYear <= vYear(Date) Then
             Sheets(i).Tab.Color = RGB(0, 0, 0)
          ElseIf vMonth = Month(Date) And vYear = Year(Date) Then
             Sheets(i).Tab.Color = RGB (0, 0, 255)
          Else: Sheets(i).Tab.Color = RGB(255, 0, 0)
          End If
       Next i
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 02-16-2013, 05:12 AM
  2. Changing Cell Color Conditionally
    By SpyPirates in forum Excel General
    Replies: 8
    Last Post: 12-06-2009, 03:00 AM
  3. changing tab color conditionally
    By ldd in forum Excel General
    Replies: 1
    Last Post: 06-13-2005, 08:50 AM
  4. Changing color conditionally
    By bhalooneel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2005, 12:05 PM
  5. Replies: 7
    Last Post: 02-02-2005, 03:06 AM

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