Excel点击单元格变绿后自动统计行内绿色单元格数量的实现问题

Hey Jake, let's get this auto-counting sorted out for you! I totally get the frustration—having to manually trigger the count module every time you color a cell is such a hassle. Let's fix that right in your existing Worksheet_SelectionChange event code so the count updates automatically the second you turn a cell green.

The core issue here is that your current color-setting code and your count module aren't connected. We can directly integrate the counting logic into the same click event that turns the cell green. Here's how to modify your existing code:

Modified Worksheet Event Code #

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Dim targetRow As Long
    Dim greenCount As Long
    Dim cell As Range
    
    ' Define your target click area
    Set rng = Range("C4:Y322")
    
    ' Only process single-cell clicks within the target range
    If Not Intersect(Target, rng) Is Nothing And Target.Cells.Count = 1 Then
        ' Set the cell to green (keep your original behavior here)
        Target.Interior.Color = RGB(0, 255, 0)
        
        ' Grab the row number of the clicked cell
        targetRow = Target.Row
        
        ' Count all green cells in the current row (C to Y columns)
        greenCount = 0
        For Each cell In Range("C" & targetRow & ":Y" & targetRow)
            If cell.Interior.Color = RGB(0, 255, 0) Then
                greenCount = greenCount + 1
            End If
        Next cell
        
        ' Write the count to a column of your choice (I used Z here—adjust to your needs)
        Range("Z" & targetRow).Value = greenCount
    End If
End Sub

Key Details to Note: #

• Auto-trigger on click: Now every time you click a cell in C4:Y322 and turn it green, the code immediately counts all green cells in that row and updates the result right away.
• Customizable count column: I used column Z for the count result, but you can change that to any column outside your click range (so you don't accidentally trigger the color change when editing the count).
• Reusable function option: If you want to keep the counting logic separate (for easier maintenance later), you can turn it into a standalone function and call it from the event:

  ' Place this in the same worksheet module or a standard module
  Function CountGreenCells(rowNum As Long) As Long
      Dim cell As Range
      Dim count As Long
      count = 0
      For Each cell In Range("C" & rowNum & ":Y" & rowNum)
          If cell.Interior.Color = RGB(0, 255, 0) Then
              count = count + 1
          End If
      Next cell
      CountGreenCells = count
  End Function
  

  Then replace the counting part in the event with:

  Range("Z" & targetRow).Value = CountGreenCells(targetRow)
  

• Bonus: Toggle green color: If you want users to be able to click a green cell to turn it back to normal (instead of only setting it to green), add this check before setting the color:

  If Target.Interior.Color = RGB(0, 255, 0) Then
      Target.Interior.Color = xlColorIndexNone
  Else
      Target.Interior.Color = RGB(0, 255, 0)
  End If
  

  This way, the count will update both when you add and remove green cells from the row.

That should solve your problem—no more manual module triggers needed!

备注：内容来源于stack exchange，提问作者Jake Parker