Vrftjkry

I'll preface this by saying that programming is not my strong suit but I would appreciate any help.

I have used the below formula in an attempt to clear the contents of cell AQ when the value of cell D (on the same row) changes. Currently, the contents are only cleared in AQ when the value in cell D changes to 1 (presumably due to the line "If Target = 1 Then". What do I use instead of "1" so that all changes, from any value to any value, will initiate the ClearContents instruction for cell AQ?

Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D7:D506")) Is Nothing Then

        If Target = 1 Then

            Range("AQ" & Target.Row).ClearContents

        End If

    End If

End Sub

To make sure a true change has occurred, we first save the value of the selected cell as a public variable. Since you can select more than one cell at once, I've decided to use a public Collection.

Then when a change occurs we compare the new value of that cell to the old value in the collection, and only if they don't match, clear the contents of AQ.
Something like:

Public coll As New Collection



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim cel As Range

    Set coll = New Collection

    If Not Intersect(Target, Range("D7:D506")) Is Nothing Then

        For Each cel In Target

            coll.Add cel.Value, cel.Address

        Next cel

    End If

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D7:D506")) Is Nothing Then

        If Not Target.Value = coll(Target.Address) Then

           Range("AQ" & Target.Row).ClearContents

        End If

    End If

End Sub

Edit updated to work when selecting multiple cells.

Obviously, if you change a value in Range("D7:D506") without actually selecting the cell first, this code will throw an error.

Come to think of it, you can edit multiple cells at once, if you delete the value. Then this code will also throw an error.
But we can work around that by using the same approach in Worksheet_Change as we are doing in Worksheet_SelectionChange:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel As Range

    If Not Intersect(Target, Range("D7:D506")) Is Nothing Then

        For Each cel In Target

            If Not cel.Value = coll(cel.Address) Then

                Range("AQ" & cel.Row).ClearContents

            End If

        Next cel

    End If

End Sub