Tab Color based on cell reference

[guamlenahans]

Hi,

I have a workbook with 20 sheets. 10 of those sheets are named after locations I monitor. I'm trying to color code eachtab location based on the value of cell K6. If K6 = 0 then the tab is green if not red. I have a code that turns all sheets based on this formula, but I only want to turn a certain few. Any help is appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim lColor As Long

If Len(ActiveSheet.Range("k6").Value) > 0 Then
lColor = 4
Else
lColor = 3
End If

For Each ws In Worksheets
ws.Tab.ColorIndex = lColor
Next ws

Set ws = Nothing

End Sub


Rob

 

[Sektor]

Sub ColorSpecificSheets()

    Dim arr As Variant
    Dim i As Integer, lColor As Long
    
    lColor = IIf(Not IsEmpty(Range("K6")), 4, 3)

    ' Adjust sheets indexes.
    arr = Array(1, 5, 10, 15, 18, 19, 20)
    
    For i = LBound(arr) To UBound(arr)
        Sheets(i).Tab.ColorIndex = lColor
    Next ws

End Sub

 

[guamlenahans]

Sektor,

Thanks. I keep getting an error on the next ws line.

Rob

 

[Sektor]

Delete ws.