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

DEAR, DEAR DEAR, DEAR Glebs Kozlovskis

With all due respect.

I offered you a solution to your problem in your other Post related to this one

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-active-checkbox/003c8a9f-b391-4914-bef0-3aa2ac34cffb

And I did that because I knew YOUR IDEA or YOUR APPROACH is NOT efficient

Sorry if being blunt in my comment. I don't want to be rude.

With your approach

1. You are using a helper sheet for the TRUE/FALSE calculations (a total waste of time, and memory resources for excel to auto-calculate, makes your workbook bigger)
2. 150 check-boxes with the possible aesthetic problem of alignment, size, to don't move with cells, etc...
3. Link the check-boxes to 150 cells (you might need a code for that too)
4. 150 Codes for each checkbox  (Insane)

HansV MVP has pointed out It is NOT a good idea

And I have the same opinion since the beginning

You said in another post *"It is a bit stupid..."*If so, don't make it BIGGER

Regards