Change tab colour based on text in different cells

[Talldrink]

Hi, thanks for taking my question - my first post on the site!

I would like the tab colour of one of my sheets to turn yellow only if either B9 or B11 has the word "NO" in it. Both B9 and B11 have drop down selections for either 'YES' or 'NO' - not sure if that impacts the code???

Thanks a bunch!

 

[Special-K99]

Perhaps base something on this:

http://www.mrexcel.com/forum/excel-questions/624987-how-change-tab-color-based-cell-value.html

 

[Fishboy]

Hi, thanks for taking my question - my first post on the site!

I would like the tab colour of one of my sheets to turn yellow only if either B9 or B11 has the word "NO" in it. Both B9 and B11 have drop down selections for either 'YES' or 'NO' - not sure if that impacts the code???

Thanks a bunch!

Hi TallDrink, welcome to the boards.

To be honest having done some testing when writing this code I am not sure yellow is the best choice as it is difficult to distinguish the yellow from the default colour. That said, the following Worksheet_Change code should do what you need. Right-click on the tab name, select View Code, then paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)


' If you update B9 or B11 then...
If Target.Address = "$B$9" Or Target.Address = "$B$11" Then
    ' If you select no then...
    If Target.Value = "NO" Then
        ' Change the tab colour to yellow
        ActiveSheet.Tab.ColorIndex = 6
    ' Else if you select yes then...
    Else
        ' If B9 and B11 are BOTH now yes then...
        If Application.WorksheetFunction.CountIf(Range("B9:B11"), "NO") = 0 Then
            ' Reset the tab colour to default
            ActiveSheet.Tab.ColorIndex = xlNone
        End If
    End If
End If


End Sub

 

[Talldrink]

Thanks so much, this worked beautifully!!

One question: If I select 'NO' in one of the cells, the tab changes colour as it should. If I change the cell to 'YES', then the tab goes back to the default colour, as it should. However, if I select 'NO' and then delete the cell, the tab remains yellow. Is there a way to get the tab to change colour only when 'NO' is selected (i.e. it won't stay yellow if the cell is deleted and left blank)?

Thanks again!

 

[Fishboy]

Thanks so much, this worked beautifully!!

One question: If I select 'NO' in one of the cells, the tab changes colour as it should. If I change the cell to 'YES', then the tab goes back to the default colour, as it should. However, if I select 'NO' and then delete the cell, the tab remains yellow. Is there a way to get the tab to change colour only when 'NO' is selected (i.e. it won't stay yellow if the cell is deleted and left blank)?

Thanks again!

Happy to help.

With regards to your query, maybe something like this perhaps? Changes highlighted in red:

Private Sub Worksheet_Change(ByVal Target As Range)


' If you update B9 or B11 then...
If Target.Address = "$B$9" Or Target.Address = "$B$11" Then
    ' If you select No then...
    If Target.Value = "NO" Then
        ' Change the tab colour to yellow
        ActiveSheet.Tab.ColorIndex = 6
    ' Else if you select Yes then...
    Else
        ' If NEITHER B9 or B11 are No then...
        If Target.Value = "" Or Application.WorksheetFunction.CountIf(Range("B9:B11"), "NO") = 0 Then
            ' Reset the tab colour to default
            ActiveSheet.Tab.ColorIndex = xlNone
        End If
    End If
End If


End Sub