Increase Cell by 1 if another cell changes to a certain value using macros

[ebanks90]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "C2" Then Range("F2").Value = Range("F2").Value + 1
End Sub

---

I am using the follow macros code when I right click my spreadsheet and enter it in the window there. My intention is to increase the value in F2 every time the value in C2 changes to a specific text value. For instance, if C2 has the value "Alex" I want F2 to increase by 1 every time C2 changes to say "Joe" and only Joe. If it changes from Joe back to Alex that does not need to be counted, only changing it to Joe needs to increase the value in F2 by one each time.

---

I also need to do this for all values in the spreadsheet from C2-C59 and increasing the corresponding cell in F2-F59. All help is much appreciated thank you

 

[Boswell]

This will work. Something to watch out for: If someone changes a cell value from "Joe" to "Joe" then this will increment the count. This is because the Excel 'change' event is really a 'cell edit' event and it doesn't care if the edit resulted in a different value.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

    ' value to look for ... if a changed cell contains KEY then +1 the corresponding row in offset column
    Const KEY = "Joe"
    ' user input is col C... we want to change corresponding row in F therefore col offset is 3
    Const COL_OFFSET = 3
    
    Dim userInputRng As Range: Set userInputRng = Me.Range("C2:C59")
    Dim inputTest As Range: Set inputTest = Intersect(Target, userInputRng)
    
    If inputTest Is Nothing Then Exit Sub
    
    If UCase(Target.Value) = UCase(KEY) Then Target.Offset(0, COL_OFFSET).Value = Target.Offset(0, COL_OFFSET).Value + 1
     
End Sub

 

[ebanks90]

Here is the problem I am having

 

[ebanks90]

Sorry I was having trouble editing my reply.
What I need more specifically is a macros that counts all of the rows individually for a change based on individual names to change to.

FOR EXAMPLE:

Task #1 Alex 4
Task #2 Frank 1
Task #3 Joe 1


If Task #1 goes from Alex to Joe, than C1 will need to be raised to 5. Task #3 should not be affected.
If Task #2 goes from Frank to Sam, than C2 will need to be raised to 2. If it goes back to Frank from Sam, nothing needs to happened to C2. If it goes back to Sam once more, than C2 will change to 3.
If Task #3 goes to Alex, C3 will remain at 1. If it goes back to Joe than C3 will increase to 2.

 

[Boswell]

Maybe this? Not sure if i'm interpreting things correctly. In the below code you set a KEY for each row in column C in Column B. If someone enters a value in column C that doesn't match the key in its corresponding row of column B then the count is incremented.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    
    ' value to look for ... if a changed cell contains KEY then +1 the corresponding row in offset column
    Const KEY = "Joe"
    
    ' user input is col C... we want to change count in corresponding row of F therefore count col offset is 3
    Const COUNT_COL_OFFSET = 3
    ' store KEYS in col B... therefore key col offset is -1
    Const KEY_COL_OFFSET = -1

    Dim userInputRng As Range: Set userInputRng = Me.Range("C2:C59")
    Dim inputTest As Range: Set inputTest = Intersect(Target, userInputRng)
    
    If inputTest Is Nothing Then Exit Sub
    
    If Not UCase(Target.Value) = UCase(Target.Offset(0, KEY_COL_OFFSET).Value) Then
    
        Target.Offset(0, COUNT_COL_OFFSET).Value = Target.Offset(0, COUNT_COL_OFFSET).Value + 1
        
    End If
     
End Sub

 

[ebanks90]

Unfortunately this code did not run for me, and I am unsure what you are talking about when you refer to column B.

Here is the scenario I am working with..

Each row has its own KEY to increment the counter by 1. For instance, if row 2's KEY is "Joe", when C2 updates to Joe, then F2 needs to increment by 1.

#1 Alex 1
#2 Frank 3

If "Alex" is changed to "Joe" (the KEY for row one) in row one, then the counter needs to increase to 2
If #1 is changed from "Joe" back to "Alex", the counter remains at 2

If #2 is changed from "Frank" to "Mike" (the KEY for row two), then the counter needs to increase to 4
If #2 is changed from "Mike" to "Alex", the counter should not be affected


I need a specific key for column C in each row that will increase the counter in F if column C is changed to that key

 

[Boswell]

The below code works. I am using Column B to store all the keys. Relating this to your example:

If "Alex" is changed to "Joe" (the KEY for row one) in row one, then the counter needs to increase to 2

I am storing the key for row one in the first row of column B. Therefore, in row 1 of column B I have the value 'Joe'. This allows me to easily compare a value in column C (where the user is making changes) to the appropriate key ... because each row the user can edit is aligned to a key by its row number.

Walking through the code below: when a cell value is changed the method first determines if the target is in the range C2:C59 (because you specified that as your input range). If the target is in C2:C59, then it compares target's value to the value in the corresponding row of column B (i.e. if you change C2 then the code compares the value in C2 to the value in B2) Since column B contains the KEY if C2.Value = B2.Value then the method increments the count.

I used COL_OFFSETS rather than hard coded references because it makes it easier to move your count or key column around. Just change the COL_OFFSET values to represent the number of columns your count and key are away from your input. e.g. your input is column C, if your keys are in column b then KEY_COL_OFFSET is -1, if your count is in column F then COUNT_COL_OFFSET is +3.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    
    ' user input is col C... we want to change count in corresponding row of F therefore count col offset is 3
    Const COUNT_COL_OFFSET = 3
    
    ' store KEYS in col B... therefore key col offset is -1
    Const KEY_COL_OFFSET = -1

    Dim userInputRng As Range: Set userInputRng = Me.Range("C2:C59")
    Dim inputTest As Range: Set inputTest = Intersect(Target, userInputRng)
    
    If inputTest Is Nothing Then Exit Sub
    
    If UCase(Target.Value) = UCase(Target.Offset(0, KEY_COL_OFFSET).Value) Then
    
        Target.Offset(0, COUNT_COL_OFFSET).Value = Target.Offset(0, COUNT_COL_OFFSET).Value + 1
        
    End If
     
End Sub