vba code

[subfuntres]

I have a depth sensor with an output from 0-60
I would like to add a value of 1 to another cell each time the value exceeds 5, but only once.
when the value falls below 5 the cell does not change value and waits for the value to increase above the 5 threshold.
I have tried a vba code which adds a value when increased above 5 but also adds for every other value above 5 and when it falls below 5 it reverts back to previous number.
Hope I have explained this properly.

Any advice please

 

[r1998]

Sorry, my above code was lil bit wrong
this code will work fine but it uses the cell a10

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a2")) Is Nothing Then
Set Target = Range("a2")
If Target.Value >= 5 and Target.Value <= 50 Then
Range("a10").Value = Range("a10").Value + 1
End If

If Target.Value < 5 Then
Range("a10").Value = ""
End If

If Range("a10").Value = 1 Then
Range("a1").Value = Range("a1").Value + 1
End If

End If
End Sub

 

[subfuntres]

Hi Val,

Many thanks, it works as I wanted.

 

[r1998]

Thanks

 

[subfuntres]

Hi Val,

Just one more point.
Your VBA code works great.
Now instead of entering the value in A2 I am pasting it from another cell which majes the target cell only change once no matter the vale.
Any ideas please

 

[r1998]

Hello subfuntres
I am not able to understand the question
u r pasting in a2 or a1 ?
target cell is a1 or a2 ?
if a2's value doesnt matter, then what must be in a1 ? should it be incremented by 1 ?

 

[subfuntres]

Hi again.

The cell is A2. When you enter the figures as described before the vba code works great.
Now if A2 is a cell that has a pasted value from another cell the vba code only works initially and never again
Hope you can understand what I am trying to do

Thanks

 

[r1998]

Hello subfuntres
I have this data in cells
a1 = 200
a2 = ""
b1 = 10
b2 = 2
b3 = 5
I selected b1, copied n pasted in a2, a1 became 201, then
selected b2, copied n pasted in a2, a1 remained 201, then
selected b3, copied n pasted in a2, a1 became 202
The earlier code seems to be working well when copying n pasting in a2

 

[subfuntres]

Hi Again,

Yes your last post worked OK.
Sorry my mistake- i forgot to say that the value is paste linked from say b1
If you change value again in B1 it stops working

 

[r1998]

Hello subfuntres
a1 = 200
a2 = ""
b1 = 7
I selected b1, copied n pasted in a2, a1 became 201, then i changed value of b1 to 2, then
I selected b1, copied n pasted in a2, a1 remained 201, then i changed value of b1 to 10, then
I selected b1, copied n pasted in a2, a1 became 202
code seems to be working fine , dont know y its not working for u

 

[subfuntres]

Hi again,

Did you try paste linked from b1 to a2
Then I used b1 as the variable input and with the paste link a2 changes as well
but a1 does not