在子Worksheet_Change中计数具有绿色背景值=0的单元格的代码

Question

有人能教我怎么写在子Worksheet_Change中计算绿色背景的单元格值为0的代码吗？

这是我的密码

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastRow As Long, i As Long
Dim total As Integer
lastRow = 5

For i = 2 To lastRow
    Range("B6:B6").Value = 0 + Val(Cells(i, 2).Interior.ColorIndex = 4 And Cells(i, 2).Value < 1)
Next
Range("B6").Value = total
End Sub

Answer 1

Public Sub FormattingCells()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, total As Long

    Set ws = ActiveSheet  '<< always use an explicit worksheet 
    lastRow = 5

    For i = 2 To lastRow
        With ws.Cells(i, 2)
            If .Interior.ColorIndex = 4 and .Value = 0 Then
                total = total +1
            End If
        End With
    Next
    ws.Range("B6").Value = total

End Sub