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!

 

[mlackey25]

I first placed it on the 'Start Here" sheet and when that was not working I placed it in a modules folder just to see if it would make a difference.

 

[Martvg]

Can you place the code in the "Start Here" sheet and change the value of D47 in the "Start Here" sheet?
The code seems to work for me.

 

[mlackey25]

That Worked! I do not what I did wrong You are awesome!

Now I think I can add the other few conditions that I want to add, Is there anything I need to know about adding more than one code per page?

Thanks Again!

 

[Martvg]

I'm glad it worked!

 

[Martvg]

I don't think there is anything else you need to know.
I added a few tips in the code below to get you going:

Sub Worksheet_Change(ByVal Target As Range)

    'this macro is triggered if anything is changed within the worksheet hence the name "Worksheet_Change"

    If Target.Address = "$D$47" Then 'Here we check if the cell changed is cell D47.
        If UCase(Sheets("Start Here").Range("D47").Value) = "Y" Then 'Here we check if the value of D47 Equals Y, if true the following 5 lines will be executed.
            With ActiveWorkbook 'The following lines change the colour of the tabs if the last expression was true
                .Sheets("Cover").Tab.Color = 255
                .Sheets("Cost Summary").Tab.Color = 255
                .Sheets("Automated Mailroom Service").Tab.Color = 255
                '.Sheets("x").Tab.Color = 255 'A line like this must be added if you want to change the colour of a different tab, change 255 for different colours.
            End With
        End If 'We end the If statement, the lines underneath have nothing to do with the previous if statement.
        'If UCase(Sheets("Start Here").Range("D47").Value) = "N" Then 'The following 6 lines can be used if you want to check D47 for, for example, "N"
            'With ActiveWorkbook
                '.Sheets("x").Tab.Color = 255
                '.Sheets("y").Tab.Color = 255
            'End With
        'End If
    End If 'We end the If statement which checks if the cell changed is D47.

    If Target.Address = "$D$48" Then 'this should be used if you want to check a different cell, the code inbetween this if and End if should be the same as above.

    End If

End Sub

You can always send a message if you get stuck.

 

[mlackey25]

You are awesome! Thank you so much for all your help!

I was doing great, actually to the last set of conditions that I wanted to add and I ran into a little snafu. The last condition is based on a cell with a formula, and I cannot get the code to read the formula, is there a way to address that?

 

[Martvg]

I'm glad you are using the macro to its potential, and even trying beyond it .

The code doesn't realise the target cell changed as it's a formula based on changes in different cells.

Unfortunately, I can not help without knowing the formula, would you be so kind to share the formula you are using?

 

[mlackey25]

So I am trying to make a condition based on the results chosen in a VLookup.

I wanted to use the corresponding 1-4 options to associate the if <2 then green and if >3 then red type of thing.

 

[Martvg]

Luckily you are not the only person who ever faced this problem.

excel - VBA code doesn't run when cell is changed by a formula - Stack Overflow

This page explains what you must do for this to work, so in your case (if the cell with the formula is D47) the code will be as following:

This workbook

Private Sub Workbook_Open()    PrevVal = Sheets("Start Here").Range("D47").Value 'Add the right cell in range
End Sub

Worksheet("Start here")

Private Sub Worksheet_Calculate()    If Range("D47").Value <> PrevVal Then
         If UCase(Sheets("Start Here").Range("D47").Value) = "Y" Then 'Here we check if the value of D47 Equals Y, if true the following 5 lines will be executed.
            With ActiveWorkbook 'The following lines change the colour of the tabs if the last expression was true
                .Sheets("Cover").Tab.Color = 255
                .Sheets("Cost Summary").Tab.Color = 255
                .Sheets("Automated Mailroom Service").Tab.Color = 255
                '.Sheets("x").Tab.Color = 255 'A line like this must be added if you want to change the colour of a different tab, change 255 for different colours.
            End With
        End If 'We end the If statement, the lines underneath have nothing to do with the previous if statement.
    End If
End Sub

Module1

Public PrevVal As Variant

You can leave the Worksheet_Calculate underneath the Worksheet_Change if you wish to keep the Worksheet_Change.

Best regards,