CODEWITHSUNDEEP
excelvbaloops
Alex
Alex

Reputation: 55

Coloring Excel Tabs based on cell value on each sheet

Trying to have VBA loop through sheets and color tab red if K1 on each sheet is > 0. What I have so far fails to loop through sheets, just colors active sheet:

    Sub IfJNegRedTab()
'
'
'
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets

Range("K1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],""<0"")"

If Range("K1").Value > 0 Then
With ActiveWorkbook.ActiveSheet.Tab
        .Color = 255
        .TintAndShade = 0
End With
End If
Next sht
'
End Sub

Upvotes: 1

Views: 97

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

Always make sure you're referencing the intended sheet. Having simply Range(A1) will refer to the active sheet. You need to do Sheets("MySheet").Range(A1). Note it's also best practice to avoid using .Select/.Activate

Sub IfJNegRedTab()
Dim sht     As Worksheet

For Each sht In ActiveWorkbook.Worksheets
    With sht
        .Range("K1").FormulaR1C1 = "=COUNTIF(C[-1],""<0"")"
        If .Range("K1").Value > 0 Then
            With .Tab
                .Color = 255
                .TintAndShade = 0
            End With
        End If
    End With
Next sht
End Sub

Also the formula in K1 just being used to check the column to the left's value can be removed:

Sub IfJNegRedTab_v2()
Dim sht     As Worksheet

For Each sht In ActiveWorkbook.Worksheets
    With sht
        If .Range("K1").Offset(0, -1).Value < 0 Then
            With .Tab
                .Color = 255
                .TintAndShade = 0
            End With
        End If
    End With
Next sht
End Sub

Upvotes: 1

Related Questions