Changing Tab color based on the value from one cell

[mlackey25]

Hello, I have been trying to find the answer to this exact scenario for a few days and I cannot find anything that will work.

I am trying to change the color of the Tabs within a workbook that has 50+ worksheets.

The problem that I am running into is I want all the color to be based off multiple cells on the first sheet. For example, I have one cell on the first sheet that if the value = "Y" I want 6 tabs to change color or if the value = "N" I want 4 for the same tabs to change color. I am basically trying to make this an IF-THEN workbook that anyone could step in and use within my office. The "Y" and "N" are in a dropdown list but not a formula if that makes a difference.

All of the options that I can find, changes the color of the tab based on the value of the cell in the same worksheet. This will not really work for what I am trying to do without making a cell just "view" the value from the first page.

Any help is appreciated!

 

[Martvg]

Hi Mlackey25,

Sub colour()


    If Sheets("Sheet4").Range("A1").Value = 1 Then
    
    With ActiveWorkbook
        .Sheets("Products").Tab.Color = 255
        .Sheets("Sheet3").Tab.Color = 255
        .Sheets("Sheet4").Tab.Color = 255
    End With
    
    End If
End Sub

Are you looking for something similar to this?
It checks the if he value in sheet4 equals 1, if it does then it changes the tab colour of sheet 3, sheet 4 and a sheet named "products".

Best regards,
Mart

 

[mlackey25]

Hey Mart,

This is what I am looking for, but I still cannot get it to change the colors of my tabs. I modified the code to fit my workbook but I am not sure what I did wrong. This is what I used. (I also doubled checked and I do have Macros turned on.)

Sub TabColor()    If Sheets("Start Here").Range("D47").Value = Y Then
    
    With ActiveWorkbook
        .Sheets("Cover").Tab.color = 255
        .Sheets("Cost Summary").Tab.color = 255
        .Sheets("Automated Mailroom Service").Tab.color = 255
    End With
    
    End If
End Sub

 

[Martvg]

Hi mlackey25,

As you are working with text the macro requires "Y" instead of just Y in the "If sheets...... = Y Then" line.
That should solve your problem.

Best regards,
Mart

 

[mlackey25]

Hey Mart,

Unfortunately, that did not make any difference.

matt

 

[Martvg]

That is strange,

Is it giving an error? and, if so, on which line of code does the error occur?

 

[mlackey25]

So strange thing is happening...

When I "F8" through the code it is working, but it will not work within the worksheet when I update the cell that it is looking at. It is also Case sensitive, is there a way to change that?

 

[Martvg]

I see, you want this code to run as soon as cell D47 in sheet "Start Here" is changed.

In that case we need to create a macro capable of identifying Events, we do so by putting the code under Sheet (Start Here) in the VBE.

Could you test this?

Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$D$47" Then
        If UCase(Sheets("Start Here").Range("D47").Value) = "Y" Then
            With ActiveWorkbook
                .Sheets("Cover").Tab.Color = 255
                .Sheets("Cost Summary").Tab.Color = 255
                .Sheets("Automated Mailroom Service").Tab.Color = 255
            End With
        End If
    End If


End Sub

I believe this will fit your needs.

 

[mlackey25]

I am sorry that I did not make that clear at the beginning, I thought I had covered eveything.

I tried this, and I do not know if I am doing something wrong, but now I cannot get anything to work, I cannot even "F8" through the code in the VBE window. This code was meant to replace the prior, correct?

 

[Martvg]

Yes, it was supposed to replace the prior.

Where did you place the code? is it in a module within the modules folder in the VBE (on the left (In the project explorer window)) or is it in the sheet (Start Here) within the Microsoft Excel Objects folder (on the left (In the project explorer window)).