Change tab color with conditional formatting

Zenaida · #1 (**permalink**) 04-24-06, 12:39

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.

norie · #2 (**permalink**) 04-24-06, 15:48

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?

Zenaida · #3 (**permalink**) 04-24-06, 18:29

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!

shades · #4 (**permalink**) 04-25-06, 09:26

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.

Zenaida · #5 (**permalink**) 04-25-06, 10:12

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.

Zenaida · #6 (**permalink**) 04-26-06, 16:42

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.

norie · #7 (**permalink**) 04-26-06, 16:44

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.

Zenaida · #8 (**permalink**) 04-26-06, 16:46

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.

Fazza · #9 (**permalink**) 04-26-06, 20:03

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

Zenaida · #10 (**permalink**) 04-26-06, 20:14

Thanks so much!!! It works perfect!

Fazza · #11 (**permalink**) 04-26-06, 20:23

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,

Zenaida · #12 (**permalink**) 04-26-06, 20:25

Thanks for the tip. You know your stuff!

Fazza · #13 (**permalink**) 04-26-06, 20:26

Flattery will get you anywhere!

phill25 · #14 (**permalink**) 01-04-12, 07:02

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

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

Internet Services

Web Development

Digital Marketing

Consumer Tech