基于单元格值设置Excel标签颜色的VBA问题：遍历工作表失效

Let's break down why your current code isn't working, then fix it step by step.

What's Wrong with the Original Code? #

• You're using Range("K1"), ActiveCell, and ActiveSheet which only target the currently active worksheet, not each sheet in your loop (sht). So even though you have a For Each sht loop, you're never actually switching to or interacting with each individual sheet.
• Using Select is unnecessary here—it slows down your code and introduces unnecessary dependencies on the active sheet.

Corrected Code #

Sub IfJNegRedTab()
    Dim sht As Worksheet
    
    ' Loop through every worksheet in the active workbook
    For Each sht In ActiveWorkbook.Worksheets
        ' Set the formula in K1 of the current sheet (no need to select!)
        sht.Range("K1").FormulaR1C1 = "=COUNTIF(C[-1],""<0"")"
        
        ' Check if K1's value is greater than 0
        If sht.Range("K1").Value > 0 Then
            ' Color the tab of the CURRENT sheet (sht) red
            With sht.Tab
                .Color = 255
                .TintAndShade = 0
            End With
        Else
            ' Optional: Reset tab color if K1 is not >0
            sht.Tab.ColorIndex = xlColorIndexNone
        End If
    Next sht
End Sub

Key Changes Explained: #

• sht.Range("K1") instead of Range("K1"): This explicitly tells VBA to target cell K1 on the current sheet in the loop (sht), not the active sheet.
• Removed Select and ActiveCell: We directly assign the formula to sht.Range("K1") without selecting anything—this is faster and more reliable.
• sht.Tab instead of ActiveWorkbook.ActiveSheet.Tab: Now we're modifying the tab color of the sheet we're currently looping through, not whatever sheet happens to be active.
• Added an optional Else clause: Resets the tab color to default if K1 isn't greater than 0, so tabs don't stay red if the condition changes later.

Bonus Tip #

If you don't need to keep the COUNTIF formula in K1 after running the macro, you can calculate the value directly in VBA without writing a formula to the sheet. This keeps your worksheet cleaner:

Sub IfJNegRedTab_NoFormula()
    Dim sht As Worksheet
    Dim negCount As Long
    
    For Each sht In ActiveWorkbook.Worksheets
        ' Calculate the count of negative values in column J directly in VBA
        negCount = Application.WorksheetFunction.CountIf(sht.Columns("J"), "<0")
        
        If negCount > 0 Then
            sht.Tab.Color = 255
            sht.Tab.TintAndShade = 0
        Else
            sht.Tab.ColorIndex = xlColorIndexNone
        End If
    Next sht
End Sub

内容的提问来源于stack exchange，提问作者Alex