VBA - Changing the colour of multiple worksheet tabs depending on a cell value

Question

I am trying to create a button that upon one click, will check a specific cell in 10 sheets of a workbook, and recolour the tabs depending on a cell value.

For example,

• If cell E15 > 18, then the tab colour should turn green.
• If cell E15 < 18, then the tab colour should turn red.

All 10 tabs should be evaluated and recoloured upon a single button click.

So far my macro looks like this, giving just three sheets for an example. It's very crude but I am very new to VBA (1 day).

My main issue is that it works for the first tab, but then opens the second tab and says "Object Required"

Sub Update_Tab_Colour_One_Click()    
    Sheets(4).Activate    
    If Cells(13, 11).Value > 18 Then
        With ActiveWorkbook.ActiveSheet.Tab
            .Color = vbGreen
        End With
    Else
        With ActiveWorbook.ActiveSheet.Tab
            .Color = vbRed
        End With
    End If    

    Sheets(5).Activate    
    If Cells(13, 11).Value > 18 Then
        With ActiveWorkbook.ActiveSheet.Tab
            .Color = vbGreen
        End With
    Else
        With ActiveWorbook.ActiveSheet.Tab
            .Color = vbRed
        End With
    End If

    Sheets(6).Activate    
    If Cells(13, 11).Value > 18 Then
        With ActiveWorkbook.ActiveSheet.Tab
            .Color = vbGreen
        End With
    Else
        With ActiveWorbook.ActiveSheet.Tab
            .Color = vbRed
        End With
    End If    
End Sub

Answer 1

See if this works for you:

Sub Update_Tab_Colour_One_Click()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Index = 4 Or ws.Index = 5 Or ws.Index = 6 Then
        If ws.Cells(13, 11).Value > 18 Then
            ws.Tab.Color = vbGreen
        Else
            ws.Tab.Color = vbRed
        End If
    End If
Next ws

End Sub

It checks to see if it's the 4, 5, or 6th indexed sheet, then checks the cell value and colors the tab accordingly.

Answer 2

Something like this would probably do it.

Dim sh as Worksheet
Dim s as Long

For s = 4 to 13 ' Modify if needed
    Set sh = ThisWorkbook.Worksheets(s)
    With sh
        .Tab.Color = IIF(.Cells(13,11).Value > 18, vbGreen, vbRed)
    End With
Next

Here we've created a For/Next loop over the sheets indexed 4:13 (10 sheets, incremented by 1). Then, we set a Worksheet variable (sh) to represent the current sheet (note that it is not needed to be Active), then set the sh.Tab.Color based on the boolean expression in the IIF function to return either vbGreen or vbRed.

Info:

For..Next statement reference

IIF function reference

How to avoid using Select in Excel VBA

Answer 3

Looping over non-consecutive and named sheets

This alternative lets you loop over sheets which aren't consecutive (so 2,4,7 not just 1,2,3) and by their names (like "Sheet1", "Sheet2"). So it is much more flexible!

It happens to be just as short to write as a straight forward loop, we are just looping over an array of sheet names or numbers instead.

I have added comments to explain what each line does, see below:

Sub Update_Tab_Colour_One_Click()
    ' Declare array of sheet numbers and/or names
    Dim mySheets As Variant
    mySheets = Array(2, 4, "Sheet1")
    ' Loop over sheet numbers / names
    Dim i As Long
    For i = LBound(mySheets) To UBound(mySheets)
        ' Use With so we don't have to repeatedly say we are within this sheet
        With ThisWorkbook.Sheets(mySheets(i))
            ' Use IIF to concisely assign a conditional value
            .Tab.Color = IIf(.Cells(13, 11).Value > 18, vbGreen, vbRed)
        End With
    Next i
End Sub