Change Tab colour based of multiple cell values

[qamex]

Hi team,

I've managed to change the Tab colour based of the results of an existing macro in cell "D9"
I also need it to look at a second cell "G31" and if there is an "x" in it. The Tab should turn Blue.
I also want the purple formatting to take priority if they clash.
e.g. If the Macro returns a value that forces the purple colour and there is also an X in "G31" it will ignore the "x" and maintain the purple colour.

I'm not great at writing code but I can read and understand enough to modify what I find online. So please explain the solution as you would a child.

The code I'm currently using is;
Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("D9").Text
With ActiveSheet.Tab
Select Case MyVal
Case "Unsatisfactory Performance"
.ColorIndex = 29
Case "Considerable Improvement Required"
.ColorIndex = 29
Case "Some Improvement Required"
.ColorIndex = 29
Case "Fulfils Expectations"
.ColorIndex = xlColorIndexNone
Case "Exceeds Expectations"
.ColorIndex = xlColorIndexNone
Case "Outstanding Performance"
.ColorIndex = xlColorIndexNone
Case Else
.ColorIndex = xlColorIndexNone
End Select
End With
End Sub


Appreciate the help.

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#008000]    'If the cell that was changed was cell D9 or G31[/COLOR]
    If Target.Address(0, 0) = "D9" Or Target.Address(0, 0) = "G31" Then
        With ActiveSheet.Tab
            Select Case Range("D9").Value
                Case "Unsatisfactory Performance", "Considerable Improvement Required", "Some Improvement Required"
                    .ColorIndex = 29
                Case Else
                    If Range("G31").Value = "x" Then
                        .Color = vbBlue
                    Else
                        .ColorIndex = xlColorIndexNone
                    End If
            End Select
        End With
    End If
End Sub

 

[JoeMo]

If the only cases you want to cause a purple tab are the first three in your code, this shorter version should do what you want with respect to the purple taking precedence over blue.

Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("D9").Text
With Me.Tab
Select Case MyVal
    Case "Unsatisfactory Performance"
    .ColorIndex = 29
    Case "Considerable Improvement Required"
    .ColorIndex = 29
    Case "Some Improvement Required"
    .ColorIndex = 29
    Case Else
    If UCase([G31]) = "X" Then
        .Color = vbBlue
    Else
        .ColorIndex = xlColorIndexNone
    End If
End Select
End With
End Sub

 

[qamex]

JoeMo, works an absolute treat. Champion mate.

Love this site and the community.

If the only cases you want to cause a purple tab are the first three in your code, this shorter version should do what you want with respect to the purple taking precedence over blue.

Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("D9").Text
With Me.Tab
Select Case MyVal
    Case "Unsatisfactory Performance"
    .ColorIndex = 29
    Case "Considerable Improvement Required"
    .ColorIndex = 29
    Case "Some Improvement Required"
    .ColorIndex = 29
    Case Else
    If UCase([G31]) = "X" Then
        .Color = vbBlue
    Else
        .ColorIndex = xlColorIndexNone
    End If
End Select
End With
End Sub