# Excel: Counting how many times a cell is changed to a certain value using a formula and not cell selection.

Hello Everyone First of all i want to thank the community for all the help you guys have given me indirectly. I usually browse around for hours looking for an answer that suits my needs instead of creating a new thread, but this time I am pretty lost. Basically I am going to use a hand held scanner to input data into one cell (B1) in a spreadsheet. The data is going to be composed of natural numbers(no fractions/negatives). I wrote a formula [I][=IF(C3=\$B\$1,"X"," ")][/I] in the "B" row(B3:B650) that checks weather the adjacent number in the "C" row(C3:C650) matches the value of the (B1) cell. If it does, then it returns an "X". If it doesn't then it returns a blank value.

 A B C D E 1 SCANNER INPUT 2 COUNTER SCANNED NUMBER 3 [I]=IF(C3=\$B\$1,"X"," ")[/I] 10 4 [I]=IF(C4=\$B\$1,"X"," ")[/I] 11 5 [I]=IF(C5=\$B\$1,"X"," ")[/I] 12 6 [I]=IF(C6=\$B\$1,"X"," ")[/I] 13 7 [I]=IF(C7=\$B\$1,"X"," ")[/I] 14
I have been using the following code which i found in another thread: -------------------------- [I]Private Sub Worksheet_Change(ByVal Target As Range)[/I] [I] If Target.Count > 1 Then Exit Sub[/I] [I] ' value to look for ... if a changed cell contains KEY then +1 the corresponding row in offset column[/I] [I] Const KEY = "X"[/I] [I] ' user input is col C... we want to change corresponding row in F therefore col offset is 3[/I] [I] Const COL_OFFSET = -1[/I] [I] Dim userInputRng As Range: Set userInputRng = Me.Range("B3:B650")[/I] [I] Dim inputTest As Range: Set inputTest = Intersect(Target, userInputRng)[/I] [I] If inputTest Is Nothing Then Exit Sub[/I] [I] If UCase(Target.Value) = UCase(KEY) Then Target.Offset(0, COL_OFFSET).Value = Target.Offset(0, COL_OFFSET).Value + 1[/I] [I]End Sub ----------------------------[/I] The problem is that the code only executes the counting whenever i select the cell and either type an "X" in it or if it already has "X" and i press enter. I want the code to do the counting if the cell is modified by the formula in the "B" column. ie. If i scan an 11 into B2, and thus B4 goes through its "=IF" formula and returns an "X" value, i want the counter in A4 to increase. I hope I have made myself clear. Thanks in advance!