Tallying Changes in a Cell

[bb]

Almost a week ago I had asked a question on how to compare cells and return a score. the purpose of it was to help my kids with schoolwork this upcoming year. I was given 3 excellent options to use. This was one of them:

Book2
	A	B	C	D
1	Answer	Correct		score
2	a	a		55.56%
3	b	b
4	a	c
5	c	d
6	d	a
7	a	a
8	c	c
9	d	d
10	a	b
Sheet1

[/quote]



After some thought I was wondering if I could take this sheet one step further by having a column that will keep track of how many times information is entered into a cell or cells. I will give you an example and break it down as best I can.

The sheet above is Sheet1. Im going to set up a conditional format so when an answer is given in column A, it will turn green if it is correct; and red if its incorrect. Sheet1 will have only the answers in column A only. Id like to move column B and D to Sheet2. This way the answers will already be in place but not visible at the same time my son is entering his answers.

Using the chart above, lets say my son entered B in cell A2. He would know immediately thats the wrong answer as it would turn red. So he deletes his answer, enters D, the cell stays red. He then changes it to A and now has the correct answer.

Column A on Sheet2 is named "Times Changed"; and cell A2 will equal "3"; as it took him 3 tries to get the answer right. This procedure would be the same for the rest of the answers.

Is there any type of function or formula for Sheet2 A2-A10 that will allow me to tally changes in each cell from Sheet1 A2-A10, and record the number in Sheet2 A2-A10? Hopefully Ive made this as clear as possible. Any help is greatly appreciated.

Thank You in Advance,
bb

 

[bb]

thanks for your reply- I late for work so I will try it when I get home. Based on the curent layout, the answers are entered in row A. So Id probably put the start button out of the way; probaby row G. Hope that wouldnt alter things based on the code you gave but I'll try when I get home. thanks again for your help.

 

[HalfAce]

OK, to use Col.G for your start & stop cells, a few changes are in order.
Type in “Start” and “Stop” into cells G1 & G2, respectively.
Now in cell G3 enter this formula:
=IF(H2="","","Your time")
In cell H3 enter this formula:
=IF(H2="","",H2-H1).
Widen Col.H to a width of 12 and format H3 as Custom – (“type” = m:ss)
(These are just some things I forgot to mention before.)
Now you’re all set to paste this new code in your worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Columns.Count > 1 Or Target.Column <> 7 Then Exit Sub
If Target.Rows.Count > 1 Or Target.Row > 2 Then Exit Sub
Target.Offset(0, 1) = Now
With Target.Offset(0, 1)
.Value = .Value
.Value = Format(Now, "h:mm:ss AM/PM")
End With
End Sub

After this, go into your sheet and click on the start & stop cells and see what you think.
You can manually select H1 & H2 and delete them to start over for the next use. (Or for that matter, create a button from the forms toolbar and assign this code to it):
Sub ClearTimes()
[H1:H2].ClearContents
End Sub

This do what you want?
Dan

 

[bb]

I tried the formula and when I clicked cell H3 I received an error that read:

Compile error: Ambiguous name detected: Worksheet_SelectionChange

Not sure where to go. I believe I entered all info in correctly. If its ok with you, or anyone else who may have as suggestion on this, Id like to email you the file so you can look at it and perhaps see an alternative. I was looking to insert a timer on sheet 1 so my daughter can start the timer, enter in her answers, and when she's done, she stops the timer and the display would show how long it took her. The timer can be a single button to start/stop, or it can be 2 separate buttons. Either way would be fine.

Thanks,

bb