如何在单元格求和为0时更改Excel工作表标签颜色？

Hey there! Let's troubleshoot why your code isn't working to color the worksheet tab green when G30 (which sums G4:G17) equals 0. Here's what's likely going wrong, plus a working solution:

Common Issues with Your Original Code #

• Wrong event trigger: If you used Worksheet_Change, that only fires when cells are edited manually. Since G30 is a formula, you need the Worksheet_Calculate event instead (it triggers whenever the sheet recalculates).
• Incorrect module placement: If your code is in a standard module instead of the target worksheet's code module, it won't reference the right sheet.
• Unhandled errors: If G30 returns an error (like #DIV/0! or #VALUE!), your code will break instead of checking the value.

Working VBA Code #

Paste this directly into the code module of your target worksheet (not a standard module):

Private Sub Worksheet_Calculate()
    ' Handle errors in case G30 has an invalid value
    On Error Resume Next
    
    ' Check if G30 is a number and equals 0
    If IsNumeric(Range("G30").Value) And Range("G30").Value = 0 Then
        ' Set tab to bright green (adjust RGB values if needed)
        Me.Tab.Color = RGB(0, 255, 0)
    Else
        ' Reset tab color to default if sum isn't 0
        Me.Tab.ColorIndex = xlColorIndexNone
    End If
    
    ' Reset error handling to default
    On Error GoTo 0
End Sub

Step-by-Step Setup #

1. Right-click the worksheet tab you want to modify and select View Code.
2. Paste the code above into the empty code window that opens.
3. Close the VBA editor, then test by adjusting values in G4:G17 so G30 sums to 0—your tab should turn green instantly.

Bonus Tips #

• If you want a different shade of green, tweak the RGB values (e.g., RGB(146, 208, 80) for a softer lime green).
• If you need this to work across multiple sheets, you can adapt the code to use the Workbook_SheetCalculate event in the ThisWorkbook module.

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