VBA code or formula: Stop counting (updating) IF value is X

[Dedeke]

Hi there super users,

I know strange title want excel to stop counting. Gone try to explain as easy as possible. I'm keeping up the droven km of a motorcycleclub.
So i have colum A: is a fixed value (nr) Colum B: is a changing value (nr) Colum C: is the total A+B
Having more colums with IF formules in it. So Colum D: gives "25" if Colum C is 25; colum E: gives 50 when Colum C is 50 and so on.

Is it possible to have the date (extra colum or same colum does not matter) when the value 25 is reached. The same for 50 and so on.
While the value in colum C is growing (logic) i don't want the dates changing again.

Ex: C= 25 D= date 02/12/20 C= becomes 30 on date 10/12/20 D= should stay the same date as it was (02/12/20)

Already many thanks

 

[CeceR]

Two questions - is the date you want to pull already a column in your data? Also, will your values always equal your thresholds (in other words, will you have 25 and 50 in your values), or do you need, say, the last date the value didn't exceed your threshold (for instance, the values go from 23 to 27, so you'd want the date it was 23)? Hope this makes sense.

 

[Dedeke]

CeCeR,

For the time beeing there is no cell with the current date in it like Today() or Now().
The values 25 - 50 and so on will always pop up. Actualy the are the result of several IF formulas in different (hidden)colums. So the moment the IF conditions are true my colum D gives "25"
Yes it makes sense Indeed i don't need the date when it was 21 or 22 . The print screen is the actual situations of the columns (without all the hidden ones) So when the colum with the headers 25 , 50 and so on becomes "X" i need that date. And need to keep that Date so later on we can see who reached 25000 Km , 50000 Km on wich date.
Hope this make sense to you *** well. My spoken language is Dutch so i give my best.

Thx already for looking into it

 

[CeceR]

Ohhh, ok, I was picturing this completely wrong. I had thought a formula would do it, but now I think it will need to be VBA. I'm thinking something in the Worksheet Change code. Here is something I just roughed up, it would drop the dates in columns D and E, on the same row in which the value in column C hit 25 or 50. See if you can customize this to fit your data, I think it will give you what you're looking for:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 3 Then 'column 3 = C
    
        If Target.Value = 25 Then
            ActiveSheet.Cells(Target.Row, 4) = Date   'drop today's date into column D on the same row as the value that hit 25
        End If
        
        If Target.Value = 50 Then
            ActiveSheet.Cells(Target.Row, 5) = Date   'drop today's date into column E on the same row as the value that hit 25
        End If
    
    End If
    
End Sub

 

[Dedeke]

CeCeR

This works indeed for only 1 target.Column. Now in my case i need multiple target columns. First Target Column= C Target Value 25 date comes in D(ok )
Second target Column = E Target Value 50 date comes in F
Third target Column= G Target Value 100 date comes in H
So if you can change the code already for 2 target columns , i can figure it out for even more target rows (guess it is just copy paste and change the values)

Gonna try already mysel but this is going the good direction

 

[Dedeke]

CeCeR,
The more I read , the more I try the more I get confused . I will try to explain some more to get you on the good direction.
i am using this macro wich works fine. So this macro recognize the cell value "X". For your info the value "X" is the result of a formula like this: =ALS(N5="Go";"X";ALS(L5="25";"X";ALS(P5<=Q$82;O5;P5)))

Option Explicit

Sub CellColorChange()
Dim MR, Cell As Range

Set MR = Range("Q5:BS79")
For Each Cell In MR
If Cell.Value = "X" Then
Cell.Interior.ColorIndex = 15
Else
Cell.Interior.ColorIndex = 0
End If
Next
End Sub

If i try to change your code (which is working) If Target.Value = 25 into If Target.Value= "X" or X the value is not recognized. My confusion why does the macro recognize "X" as a Cell Value and your Target.Value doesn't?
So maybe there's a way to put a date based on a Cell.Value into another cell. Simular like my macro changes the background color.
Ex: In range Q5:Q10 . The cells Q1 / Q3 / Q4 having the "X" value the macro puts the date in Column R1 / R3 / R4
Hope this is usefully to come to a solution.

Many thx again spending your time trying to help me out

 

[CeceR]

Hi,

I'm going off your first response for the moment, because I think it can be as simple as copying the code for as many target columns as you need.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 3 Then 'column 3 = C
    
        If Target.Value = 25 Then
            ActiveSheet.Cells(Target.Row, 4) = Date   'drop today's date into column D on the same row as the value that hit 25
        End If
        
    End If
    
    If Target.Column = 5 Then 'column 5 = E
    
        If Target.Value = 50 Then
            ActiveSheet.Cells(Target.Row, 6) = Date   'drop today's date into column F on the same row as the value that hit 50
        End If
    
    End If

    If Target.Column = 7 Then 'column 7 = G
    
        If Target.Value = 100 Then
            ActiveSheet.Cells(Target.Row,86) = Date   'drop today's date into column H on the same row as the value that hit 100
        End If
    
    End If

End Sub

Looking at your second message, when I try Target.Value = "X" my code recognizes it. I used this in the Worksheet Change code in the Target.Column = 3 section and it worked for me:

        If Target.Value = "X" Then
            ActiveSheet.Cells(Target.Row, 5) = Date   'drop today's date into column E on the same row as the value that hit 25
        End If

I feel like I may still be missing something, but maybe this will get you closer.

 

[Dedeke]

CeCeR,
Yes we are still missing something. All the tests i've done results most of the time in an error (Target.Value)
Uploaded a part of my file. Sheet one is a blanc one where i putted the code in it. Works fine as long as put in the values manualy.
Even if i change the target value in "x" it works. The moment the values are coming from a formula(marked in Yellow) it stops putting the date in it.

Sheet to is a part of my original file (so some formula's won't work) so maybe you get a clear vieuw of what i want to reach.
So column Q results in a "X" based on the formula. Basic it uses the column F wich is the total ammount of Km's reached. And there is also a check if 2/3 of the total km's are national (see formulas in hidden columns) At the end this doesn't matter while they are working. So i get my result "X" the moment it reaches this "X" i want the date fixed like it does in your code.

Hope this helpfull whe nyou see the original file with all the formulas in it. There is only one code in it which changes the colors off the cells marked with an "X"
And now trying to get the file also in here

 

[Dedeke]

Seem like it is not possible to upload a file

 

[CeceR]

OK, I know you weren't able to upload the file but I think I have a better idea now of what I missed from your prior posts. You mentioned the value was the result of a formula, but I completely glossed over that, I apologize.

Is the data that feeds the formula being entered manually? If so, you can change the target column to 6 (total km) and then refer column Q in the If statement. Something like this, perhaps:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 6 Then
    
        If Sheets(1).Cells(Target.Row, 17).Value = 25 Then
            ActiveSheet.Cells(Target.Row, 4) = Date   'drop today's date into column D on the same row as the value that hit 25
        End If
        
        If Sheets(1).Cells(Target.Row, 17).Value = "X" Then
            ActiveSheet.Cells(Target.Row, 5) = Date   'drop today's date into column E on the same row as the value that hit 25
        End If
    
    End If
    
End Sub

This would look at the value in column Q whenever something in column F changes.