Help needed in writing this simple formula

[Yogi Watcher]

How would I write a formula that does this:

If value of B1 is 2 or 4 or 6 then keep value of A1 as it
is, else change value of A1 to whatever value that is in
B1.

 

[Don Guillett]

If you put this in a1, it will not keep it what it was but will change to
nothing.

=if(or(b1=2,b1=4),b1,"")

 

[Guest]

Is there any way around. I would like to preserve value
of A1 instead of making it BLANK.

IF using function is not an option, then can it be done
using VBA code?

 

[Don Guillett]

This code will do it.

Sub keepitasis()
If Range("b1") = 2 Or Range("b1") = 4 Then Range("a1") = Range("b1")
End Sub

or to have it automatic, put this in the SHEET module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
If Target = 2 Or Target = 4 Then Target.Offset(0, -1) = Target
End Sub