Excel Forum

Code: Select all

'ThisWorkbook

Private Sub Workbook_Open()
    a = Sheets("Sheet2").[a1:a10].Value
    Sheets("Sheet2").[C1:C10].Value = 0
End Sub

'Module1
Public a As Variant

'Worksheet2
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
        If a(Target.Row, Target.Column) = 1 And Target.Value = 0 Then
            Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + 1
        End If
    End If
    a = Me.[a1:a10].Value
    Application.EnableEvents = True
End Sub

Code: Select all

Private Sub worksheet_calculate()
Dim Target As Range
Set Target = Range("A1")


 Application.EnableEvents = False
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Range("A1").Value = 0 Then
        Range("C1").Value = Range("C1").Value + 1
        End If
    End If

Application.EnableEvents = True

End Sub

Code: Select all

Dim r As Range
Dim c As Boolean

Private Sub worksheet_calculate()
'    Dim Target As Range
'    Set Target = Range("A1")
    If c = False Then
        c = True
        Exit Sub
    End If
    If r.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(r, Me.[k1:k10]) Is Nothing Then
        If r.Value = 0 Then
           Me.Cells(r.Row, "c").Value = Me.Cells(r.Row, "c").Value + 1
        End If
    End If
    c = False
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If c = False Then Exit Sub
    Set r = Target
    c = True
    r.Value = r.Value
End Sub