0

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
  • Why are you testing If Target = 1?  Why not just take it out and do If Not Intersect(Target, Range("D7:D506")) Is Nothing Then ... Range("AQ" & Target.Row).ClearContents? – Scott Nov 20 '18 at 2:53
  • Hi Scott, I just figured this out. I should've looked at your response an hour ago! To answer your question: that's the problem when one plagiarises all one's code from other similar examples on the web. Simple solutions are not obvious to the uninitiated. While I have you, is there code i could add that would prevent AQ from clearing if the value of D is changed to the same value. (i.e. D=1, then i input D=1 again). Currently the code can't differentiate between a true change in value (i.e. 1 to 2) and just a re-entering of the same value (i.e. 1 to 1). – MJS Nov 20 '18 at 4:09
  • Interesting question. I have fairly little experience with Worksheet_Change. I assume that you’ve verified through experimentation that the Worksheet_Change routine gets called even when a cell is “changed” to its current value? In that case, I suspect that you’re out of luck (anybody who knows better, feel free to correct me!). The one thing I can suggest is that you keep a backup copy of Column D, and compare the “new” value to your saved copy. – Scott Nov 20 '18 at 4:17
  • Thanks for the help @Scott – MJS Nov 20 '18 at 23:15
  • Thank you @Christofer Weber. I had some issues with your code but I received a (seemingly) workable solution on another forum in the meantime so hopefully won't need this now. But thank you for your time and effort. – MJS Nov 21 '18 at 10:47
0

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
  • 1
    (1) Nice. I was thinking in terms of keeping a copy of Column D somewhere else on the worksheet (e.g., Column AD); keeping it in VBA memory seems safer. (2) Between the question title and the If Not Intersect(Target, Range("D7:D506")) Is Nothing Then line, it appears that the user is concerned only with changes in Column D; specifically, in the range D7:D506.  So I would suggest that your routine should do, not For Each cel In Target, but rather For Each cel In Intersect(Target, Range("D7:D506")). – Scott Nov 26 '18 at 22:24
  • Putting the intersect in the for loop condition? Never thought of that. I like it! Very efficient. – Christofer Weber Nov 27 '18 at 23:57

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.