a latching function

[Guest]

If cell (A) = a predetermined value then cell (B) = x. A simple if
statement,but, I want cell (B) to act as a latch. Once cell (A) has
switched cell (B) to X I want cell (B) to stay in that condition even if the
value of cell (A) changes from the predetermined value. i.e. I want cell
(B) to behave like a latch, once it has been switched on it stays on
regardless. Can you help. Kind regards

 

[Guest]

Ken,

One way is by utilising a 3rd cell that initially blank and the first time
cell (a) satisfies your pre-determined value this 'flag cell' is given a
value and vell (b) is set to x. Any subsequent changes to cell(a) will be
evaluated as false by the macro because the check cell now has a value.

In the macro below 1,1 is cell(a) 1,2 is the flag cell and 1,3 is cell(b)

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(1, 1).Value = 1 And Cells(1, 2).Value = "" Then
Cells(1, 2).Value = 1
Cells(1, 3) = "x"
End If

Mike
End Sub

 

[Guest]

Thanks Mike. Sounds easy when your brain works, that where I fall down.

Thanks again and kind regards.

 

[Guest]

Your welcome and thanks for the feedback.

Thanks Mike. Sounds easy when your brain works, that where I fall down.

Thanks again and kind regards.