Auto count a cell

[dtallent]

I am tying to create a file in excel so that I can keep track of my daughter's volleyball stats. What I want to do is touch the screen and it will auto count, say it has 0, I touch the screen and it changes to 1, touch again and it goes to 2. I saw a macro that was created to allow the cell to change color, but I would rather it change to a number an keep counting as long as I touch he cell. Does anyone have a idea on how to do this? Below is the macro I found, but it just changes color.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'if cell fill is Blank, change to Brown
If Selection.Interior.Color = RGB(255, 255, 255) Then
Selection.Interior.Color = RGB(247, 150, 70)
GoTo Passem
End If
'if cell fill is Brown, change to Yellow
If Selection.Interior.Color = RGB(247, 150, 70) Then
Selection.Interior.Color = RGB(255, 255, 0)
GoTo Passem
End If
'If cell fill is Yellow, remove fill color
If Selection.Interior.Color = RGB(255, 255, 0) Then
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Passem:
End Sub

 

[My Aswer Is This]

You did not say what cell you wanted to select to keep the score.

This script works for Range("A1")
Modify if you want

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  10/2/2018  6:40:57 AM  EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.CountLarge > 1 Then Exit Sub
Target.Value = Target.Value + 1
End If
End Sub

 

[dtallent]

This is perfect. Now do I change it all cells. I guess I should have said I need it to count multiple cells. It a spreadsheet with different columns.

 

[My Aswer Is This]

What does this mean:

Now do I change it all cells.

Excel Worksheet has 1.5 million rows and 16,384 column

So you want this script to apply to about 12 billion cells?

So any cell you click on should add up the values in that cell.
​

 

[dtallent]

What does this mean:

Now do I change it all cells.

Excel Worksheet has 1.5 million rows and 16,384 column

So you want this script to apply to about 12 billion cells?

So any cell you click on should add up the values in that cell.
​

That was a typo. I have been trying to figure out to edit. It meant to say how do I change the range to count all cells.

 

[My Aswer Is This]

Your original post said:

I am tying to create a file in excel so that I can keep track of my daughter's volleyball stats. What I want to do is touch the screen and it will auto count, say it has 0, I touch the screen and it changes to 1, touch again and it goes to 2.

In your last post you said:

That was a typo. I have been trying to figure out to edit. It meant to say how do I change the range to ​

​

count all cells.​

​

Well again count all cells.

All cells???? all 12 billion cells.

How does the script know what cells to count and put the result where?​

​

 

[dtallent]

Just the cells on the spreadsheet I have made, it is roughly c6 - c40 through column g. I think I may have figured it out. I change A1 to c6:g40 and it seems to work. I a sure there is an easier way.

 

[My Aswer Is This]

Easier way?
That gives you 176 cells to click on to add up score
Still not sure what your wanting.

But if this works for you that's great.

 

[dtallent]

Your original post said:

I am tying to create a file in excel so that I can keep track of my daughter's volleyball stats. What I want to do is touch the screen and it will auto count, say it has 0, I touch the screen and it changes to 1, touch again and it goes to 2.

In your last post you said:

That was a typo. I have been trying to figure out to edit. It meant to say how do I change the range to ​

​

count all cells.​

​

Well again count all cells.

All cells???? all 12 billion cells.

How does the script know what cells to count and put the result where?​

​

It's columns c6-c40 through column g. I may have figured this out but I also created 5 sheets on this one file. I have copied the script to each sheet but it only works on the first sheet.

 

[dtallent]

Easier way?
That gives you 176 cells to click on to add up score
Still not sure what your wanting.

But if this works for you that's great.

I have it working perfectly. Thanks for your help. It was greatly appreciated.