Hi all,
I am trying to setup a worksheet that has three columns. In cell A2, it is a binary number that is auto-populated by the code; in B2, it is a counter that counts the number of times cell A2 changes; and in C2, I have an if/then formula that outputs a binary number based upon a cell in another worksheet. I've written the VBA code so that when cell C2 is 1 it triggers cell A2 to be filled with 1, and if not, it will be 0. Subsequently, whenever cell A2 is 1, cell B2 will count each instance and keep record of the number of times A2 has been 1. Unfortunately, it seems that cell B2 only recognizes that cell has changed to a 1 when I manually enter 1 in A2 and hit enter. Can someone help me understand why B2 isn't automatically recognizing when cell A2 automatically changes to a 1 (when the code auto-populates it based on the logic in C2)? Thanks so much!
I am trying to setup a worksheet that has three columns. In cell A2, it is a binary number that is auto-populated by the code; in B2, it is a counter that counts the number of times cell A2 changes; and in C2, I have an if/then formula that outputs a binary number based upon a cell in another worksheet. I've written the VBA code so that when cell C2 is 1 it triggers cell A2 to be filled with 1, and if not, it will be 0. Subsequently, whenever cell A2 is 1, cell B2 will count each instance and keep record of the number of times A2 has been 1. Unfortunately, it seems that cell B2 only recognizes that cell has changed to a 1 when I manually enter 1 in A2 and hit enter. Can someone help me understand why B2 isn't automatically recognizing when cell A2 automatically changes to a 1 (when the code auto-populates it based on the logic in C2)? Thanks so much!
VBA Code:
Private Sub worksheet_Activate()
If Range("C2").Value = 1 Then
Range("A2") = "1"
ElseIf Range("C2").Value = "0" Then
Range("A2") = "0"
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Call Macro1(Target)
Call Macro2(Target)
End Sub
Sub Macro1(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If Range("C2").Value = 1 Then
Range("A2") = "1"
ElseIf Range("C2").Value = "0" Then
Range("A2") = "0"
End If
Application.EnableEvents = True
Exit Sub
End Sub
Sub Macro2(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
If UCase(Target) = 1 Then
Range("B2").Value = Range("B2").Value + 1
End If
End Sub