Excel VBA code - HELP

Question

Okay, first of all - I am not an expert in VBA codes, so it is already a challenge for me. 

Let's say, I have 2 cells A1 and B1

I want to make it that:

If in A1 appears "TRUE" - B1 - shows the date and time of input 

If in A1 appears "FALSE" - B1 - shows "not yet" 

I want it to be automated (without  ActiveX/Form Controls) - so you enter the text and it is automatically works.

My code currently is:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("A1:A15")) Is Nothing Then

        With Target(1, 2)

        .Value = Date & " " & Time

        .EntireColumn.AutoFit

        End With

    End If

End Sub

I took it from tutorial and I am not sure, that it works properly, but it changes the cell as I want it (if I input something next to it), but it doesn't react if the cell "True" changes to "False" and, unfortunately, I am failed to figure out how to make it work.

Answer 1

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    If Not Intersect(Target, Range("A1:A15")) Is Nothing Then

        Application.ScreenUpdating = False

        Application.EnableEvents = False

        For Each rng In Intersect(Target, Range("A1:A15"))

            If rng.Value = True Then

                If rng.Offset(0, 1).Value = "" Or LCase(rng.Offset(0, 1).Value) = "not yet" Then

                    rng.Offset(0, 1).Value = Now

                Else

                    ' Value had already been set, so leave it alone

                End If

            Else

                rng.Offset(0, 1).Value = "not yet"

            End If

        Next rng

        Application.EnableEvents = True

        Application.ScreenUpdating = True

    End If

End Sub

Answer 2

Do you mean that the cells in A1:A15 contain formulas? The Worksheet_Change event procedure does indeed ignore formulas. Instead of referring to A1:A15 in the code, you could refer to the cells that are edited by the user and that determine the return value of the formulas in A1:A15.

Answer 3

Everything was beautiful and worked properly, unless... 

This "TRUE" and "FALSE" option are changed automatically and script, unfortunately, doesn't react on it. 

The code only works, if I enter text manually, but if it happens manually it doesn't understand it all and nothing changes...

Answer 4

If you want to apply this to A1:A15 and K1:K15 (setting the time in B1:B15 and K1:K15, respectively), change both occurrences of Range("A1:A15") in the code to Range("A1:A15,K1:K15").

If you want something else, please explain.

Answer 5

Thank you very much! 

That is exactly, what I wanted. 

Short additional question: if I want to apply it to one more column, where should  I put the additional range?