Reset cell value to 0 everytime value reaches 4

[nt82]

Hello,

I've a cell C1 which has a value through formulas ( A1+ B1). Since everytime value in A1 or B1 changes then value in C1 changes as well.

Now, I need a macro which should automatically reset cell A1 and B1 value to "1" whenever value in cell is "5"

Hope this makes sense. thanks in advance

NT

 

[Michael M]

Paste this in the sheet module

Sub worksheet_selectionchange(ByVal target As Range)
Set target = Range("C1")
    If target.Value > 4 Then
        Range("A1:B1").Value = 1
    End If
End Sub

 

[nt82]

Awesome..many thanks amigo

 

[Peter_SSs]

That may be sufficient for your needs but there are circumstances where that code will fail because it uses the Selection_Change event and not the Calculate event.

For example try these actions.

1. Select A1:B1, type 6, press Ctrl+Enter. (Result is 12 in C1 and A1:B1 not reset.)

2. Clear A1:B1 then enter 2 in A1, select B1, type 6 and confirm by clicking the 'tick' just to the left of the formula bar. (Result is 8 in C1 and A1:B1 not reset)

3. Type 7 in G1 & H1, select G1:H1, Copy, select A1:B1, Paste. (Result is 14 in C1 and A1:B1 not reset).

I think this would be more robust ..

Private Sub Worksheet_Calculate()
    If Range("C1").Value > 4 Then
        Range("A1:B1").Value = 1
    End If
End Sub