CODEWITHSUNDEEP
excelcolorstabs
MEB
MEB

Reputation: 1

Change tab color, based on data in column

How can I change the color of a tab in excel 2010, based on the data in Column D? ie... if a cell in column D contains "F" or "f", then change the tab to red, then if a cell in column D contains "PWE" or "pwe", then change the tab to yellow, then if "P" or "p" change to green, if none of those, then don't change the tab color. Help... Thanks!!

Sub tabby()

    If Range("D:D").Text = "F" Or "f" Then
        ActiveWorkbook.Tab.Color = RGB(255, 0, 0)
    ElseIf Range("D:D").Text = "PWE" Or "pwe" Then
        ActiveWorkbook.Tab.Color = RGB(255, 255, 0)
    ElseIf Range("D:D").Text = "P" Or "p" Then
        ActiveWorkbook.Tab.Color = RGB(0, 255, 0)
    Else
        ActiveWorkbook.Tab.Color = RGB(0, 0, 0)
    End If

End Sub

Upvotes: 0

Views: 438

Answers (1)

user4039065
user4039065

Reputation:

I've used the native worksheet COUNTIF function to determine if the terms are found in column D and converted the If... ElseIf ... ElseIf ... to a Select Case. The search terms are in an array that get looped through.

Sub tabby()
    Dim w As Long, v As Long, vTERMs As Variant
    vTERMs = Array("f", "pwe", "p")
    For w = 1 To Sheets.Count
        With Sheets(w)
            For v = LBound(vTERMs) To UBound(vTERMs)
                If CBool(Application.CountIf(.Columns(4), vTERMs(v))) Then
                    Select Case v
                        Case 0
                            .Tab.Color = RGB(255, 0, 0)
                        Case 1
                            .Tab.Color = RGB(255, 255, 0)
                        Case 2
                            .Tab.Color = RGB(0, 255, 0)
                    End Select
                    Exit For
                Else
                    .Tab.Color = RGB(0, 0, 0)
                End If
            Next v
        End With
    Next w
End Sub

Note that the order of the terms is important. If one term is found, the loop is exited so priority terms to search for should be first. I've used the order that you had in your sample code for the above.

Upvotes: 1

Related Questions