Counting cell changes in range of cells

[pinchy1964]

Hi all,

I'm new to VB so may be asking a daft question but hey, if I don't ask, I won't know

I am trying to add counters to a simple spreadsheet where each time a cell is modified, the cell next to it will increment.

I can do it for a single row but would like a simple way of applying to every row in a range without having to have a huge number of lines to apply the count to each row eg for a single row I am using -

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then [B1].Value = [B1].Value + 1

End Sub

But would like to apply to all rows up to row 100 for example -

If Target.Address = "$A$100" Then [B100].Value = [B100].Value + 1

In this example, how can I apply this count without having 100 lines of code (one for each row) - Hope I'm making sense

Thanks.

 

[Lewiy]

Hi and welcome to the board!!

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Cells(Target.Row, 2) = Cells(Target.Row, 2) + 1
End If
End Sub

 

[pgc01]

Hi pinchy1964
Welcome to the board

But would like to apply to all rows up to row 100

If you just want it up to row 100 (and not below), also:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then _
    Cells(Target.Row, "B") = Cells(Target.Row, "B") + 1
End Sub

Hope this helps
PGC

 

[Peter_SSs]

pinchy1964

Welcome to the MrExcel board!

Is it possible that multiple cells could be changed at once? If so, code modification would be needed. Maybe something like:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("A1:A100")).Address = Target.Address <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Target
            c.Offset(, 1).Value = c.Offset(, 1).Value + 1
        <SPAN style="color:#00007F">Next</SPAN> c
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[pinchy1964]

Thanks very much all, i've used the first version and its works great for my current needs.