Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136

    Unanswered: Change tab color with conditional formatting

    Is it possible to change the color of a tab based on a value in a cell? If so, how would I write the code?

    I have six spreadsheets in a workbook. If there is a value in cell V1 of the first spreadsheet, I would like all six tabs to be red. If cell V1 is blank, I don't want any coloring.

    Any assistance is appreciated.

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    What version of Excel do you have?

    It's only later (after 2000?) that will allow you to change tab colour.

    The way to do ot would be through a worksheet event.

    Which event to use would depend on how V1 will change.

    Is it a formula or is it input manually?

  3. #3
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Sorry, forgot to mention - I'm using Excel 2002. The spreadsheet is a little complex so I've attached it here if you could look at it. Cell V1 is merged and it's a date format.

    I already have a code in the worksheet event, so I also need help in where to plug the code in for this. Thanks so much!
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Just as a note. It is very good practice to AVOID MERGED CELLS. They cause all kinds of problems and interfer with many Excel capabilities. Far better to use "Center Across Selection" which accomplishes the same thing and allows Excel to use all of its capabilities.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thanks, I wasn't aware of the many issues merged cells caused. I changed cell V1 so it's no longer merged.

    I'll state my situation again. I would like to change the tab color of all 6 worksheets in my workbook. If there is a value in cell V1 of the first worksheet, I would like all 6 tabs to be red. If cell V1 is blank, I don't want any coloring on any of the 6 tabs.

    I attached my workbook to post #3 of this thread.

    Thanks again.
    Last edited by Zenaida; 04-25-06 at 15:31.

  6. #6
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Here's the code I've been using but no matter what I have in cell V1, all the tabs are red.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ws As Worksheet
        Dim i As Long
    
        If InStr(Target.Address, "$A$7") <> 0 Or InStr(Target.Address, "$A$8") <> 0 Then
            For Each ws In Worksheets
                i = i + 1
                On Error Resume Next
                If Not IsDate(Range("A7")) Then
                    ws.Name = "Cert Period " & i
                Else
                     ws.Name = Format(ws.Range("A7"), "m-dd-yy") & " THRU " & Format(ws.Range("F7"), "m-dd-yy")
                End If
                If Err.Number <> 0 Then
                    MsgBox "Could not rename sheet " & ws.Name, vbCritical, "Renaming Error"
                    Err.Clear
                End If
            If Not IsDate(Range("V1")) Then
                ws.Tab.ColorIndex = -xlColorIndexAutomatic
            Else
                ws.Tab.ColorIndex = 3
            End If
    Next ws
    
        End If
    End Sub

    All tabs for all 6 worksheets are red which is what I want when there is a value in cell V1. I need to fix it so there's no color if cell V1 is empty.

    FYI - if cell V1 in the first worksheet has a value in it, that value is copied to cell V1 of the rest of the worksheets. So it doesn't matter which worksheet is referenced, if there's a value in cell V1 of any of the worksheets, I want all tabs to be red. If cell V1 of any of the worksheets is empty, I want all the tabs to not be colored.

    Any suggestions are greatly appreciated.
    Last edited by Zenaida; 04-26-06 at 17:48.

  7. #7
    Join Date
    Mar 2006
    Posts
    163
    I can't help you with the code to change the tab colour, I'm on 2000.

    But I don't think your logic right in the posted code.

  8. #8
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thanks for your advice. I received help from someone on this forum for the code I have. I'll see what I can figure out for the coloring of the tabs. Thanks again.

  9. #9
    Join Date
    Feb 2006
    Posts
    113
    Zenaida,

    I haven't tried to work out why your current code doesn't work. I have written some simple code that seems to do what you want. Maybe you can sort it out using this?

    regards,
    Fazza

    PHP Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        
    Dim ws As Worksheet
        Dim lColor 
    As Long
        
        
    If Len(ActiveSheet.Range("V1").Value) > 0 Then
            lColor 
    3
        
    Else
            
    lColor = -4142
        End 
    If
        
        For 
    Each ws In Worksheets
            ws
    .Tab.ColorIndex lColor
        Next ws
        
        Set ws 
    Nothing

    End Sub 

  10. #10
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thanks so much!!! It works perfect!

  11. #11
    Join Date
    Feb 2006
    Posts
    113

    Another thought too

    You're welcome, Zenaida.

    Another step for you might be to change the hard coded "V1" address.

    You can imagine that if another column or row is inserted before/above "V1" then whatever is now in "V1" will be in a different cell address and the code will not give you the desired results.

    You might overcome this by giving the cell a name. Such as "WhateverName". Via shortcut keys CTRL-F3 or otherwise Insert-Name-Define, etc.

    Then change the code from ActiveSheet.Range("V1")
    to ActiveSheet.Range("WhateverName")

    Then as new rows/columns are added the reference will still be correct.

    Cheers,

  12. #12
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thanks for the tip. You know your stuff!

  13. #13
    Join Date
    Feb 2006
    Posts
    113
    Flattery will get you anywhere!

  14. #14
    Join Date
    Jan 2012
    Posts
    1

    Hi!!

    How do I adapt this code so that it works for a workbook that has multiple sheets but I need each tab to be coded individually.

    So that on each tab if cell L2 is more than 0 I need the tab to go red.

    Please help if you can.

    Many thanks in advance

    Phill

    Quote Originally Posted by Fazza View Post
    Zenaida,

    I haven't tried to work out why your current code doesn't work. I have written some simple code that seems to do what you want. Maybe you can sort it out using this?

    regards,
    Fazza

    PHP Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        
    Dim ws As Worksheet
        Dim lColor 
    As Long
        
        
    If Len(ActiveSheet.Range("V1").Value) > 0 Then
            lColor 
    3
        
    Else
            
    lColor = -4142
        End 
    If
        
        For 
    Each ws In Worksheets
            ws
    .Tab.ColorIndex lColor
        Next ws
        
        Set ws 
    Nothing

    End Sub 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •