Code not behaving properly ?!?!?!?!

Kevin0427

Board Regular
Joined
Mar 31, 2016
Messages
69
This code seems to be erratic in its behavior. Sometimes when I change a cell the ones that are not in the range i select get cleared. When I go back and fix the entry it seems to work fine and then when I change another one if might clear something else. The concept was that if I changed a selection all the sub-selections would be cleared but only in that section and not other sections.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
If Target = Range("D9") Then
    Range("D11").ClearContents
    Range("D13").ClearContents
    Range("D15").ClearContents
    Range("D17").ClearContents
End If
If Target = Range("D11") Then
    Range("D13").ClearContents
    Range("D15").ClearContents
    Range("D17").ClearContents
End If
If Target = Range("D13") Then
    Range("D15").ClearContents
    Range("D17").ClearContents
End If
If Target = Range("D15") Then
    Range("D17").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
If Target = Range("D24") Then
    Range("D26").ClearContents
    Range("D28").ClearContents
    Range("D30").ClearContents
    Range("D32").ClearContents
End If
If Target = Range("D26") Then
    Range("D28").ClearContents
    Range("D30").ClearContents
    Range("D32").ClearContents
End If
If Target = Range("D28") Then
    Range("D30").ClearContents
    Range("D32").ClearContents
End If
If Target = Range("D30") Then
    Range("D32").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 
If Target = Range("D39") Then
    Range("D41").ClearContents
    Range("D43").ClearContents
    Range("D45").ClearContents
    Range("D47").ClearContents
End If
If Target = Range("D41") Then
    Range("D43").ClearContents
    Range("D45").ClearContents
    Range("D47").ClearContents
End If
If Target = Range("D43") Then
    Range("D45").ClearContents
    Range("D47").ClearContents
End If
If Target = Range("D45") Then
    Range("D47").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4[/URL] 
If Target = Range("D54") Then
    Range("D56").ClearContents
    Range("D58").ClearContents
    Range("D60").ClearContents
    Range("D62").ClearContents
End If
If Target = Range("D56") Then
    Range("D58").ClearContents
    Range("D60").ClearContents
    Range("D62").ClearContents
End If
If Target = Range("D58") Then
    Range("D60").ClearContents
    Range("D62").ClearContents
End If
If Target = Range("D60") Then
    Range("D62").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5]#5[/URL] 
If Target = Range("D69") Then
    Range("D71").ClearContents
    Range("D73").ClearContents
    Range("D75").ClearContents
    Range("D77").ClearContents
End If
If Target = Range("D71") Then
    Range("D73").ClearContents
    Range("D75").ClearContents
    Range("D77").ClearContents
End If
If Target = Range("D73") Then
    Range("D75").ClearContents
    Range("D77").ClearContents
End If
If Target = Range("D75") Then
    Range("D77").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=6]#6[/URL] 
If Target = Range("D84") Then
    Range("D86").ClearContents
    Range("D88").ClearContents
    Range("D90").ClearContents
    Range("D92").ClearContents
End If
If Target = Range("D86") Then
    Range("D88").ClearContents
    Range("D90").ClearContents
    Range("D92").ClearContents
End If
If Target = Range("D88") Then
    Range("D90").ClearContents
    Range("D92").ClearContents
End If
If Target = Range("D90") Then
    Range("D92").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7]#7[/URL] 
If Target = Range("D99") Then
    Range("D101").ClearContents
    Range("D103").ClearContents
    Range("D105").ClearContents
    Range("D107").ClearContents
End If
If Target = Range("D101") Then
    Range("D103").ClearContents
    Range("D105").ClearContents
    Range("D107").ClearContents
End If
If Target = Range("D103") Then
    Range("D105").ClearContents
    Range("D107").ClearContents
End If
If Target = Range("D105") Then
    Range("D107").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8]#8[/URL] 
If Target = Range("D114") Then
    Range("D116").ClearContents
    Range("D118").ClearContents
    Range("D120").ClearContents
    Range("D122").ClearContents
End If
If Target = Range("D116") Then
    Range("D118").ClearContents
    Range("D120").ClearContents
    Range("D122").ClearContents
End If
If Target = Range("D118") Then
    Range("D120").ClearContents
    Range("D122").ClearContents
End If
If Target = Range("D120") Then
    Range("D122").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9]#9[/URL] 
If Target = Range("D129") Then
    Range("D131").ClearContents
    Range("D133").ClearContents
    Range("D135").ClearContents
    Range("D137").ClearContents
End If
If Target = Range("D131") Then
    Range("D133").ClearContents
    Range("D135").ClearContents
    Range("D137").ClearContents
End If
If Target = Range("D133") Then
    Range("D135").ClearContents
    Range("D137").ClearContents
End If
If Target = Range("D135") Then
    Range("D137").ClearContents
End If
'Item [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 0
If Target = Range("D144") Then
    Range("D146").ClearContents
    Range("D148").ClearContents
    Range("D150").ClearContents
    Range("D152").ClearContents
End If
If Target = Range("D146") Then
    Range("D148").ClearContents
    Range("D150").ClearContents
    Range("D152").ClearContents
End If
If Target = Range("D148") Then
    Range("D150").ClearContents
    Range("D152").ClearContents
End If
If Target = Range("D150") Then
    Range("D152").ClearContents
End If
Application.EnableEvents = True
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Please outline for us a specific example of one that is not working correctly.
What are you updating?
What happens?
What should happen?
 
Upvote 0
You are comparing the values in the cells not their address.
Try using
Code:
If Target.Address(0, 0) = "D9" Then
 
Upvote 0
Please outline for us a specific example of one that is not working correctly.
What are you updating?
What happens?
What should happen?

For example when I change D24 sometimes D11:D17 might be cleared. Should only clear D26:D32. But not every time and other entries might clear the ones above also. But it is not always D11:D17 that gets cleared.
 
Upvote 0
You are comparing the values in the cells not their address.
Try using
Code:
If Target.Address(0, 0) = "D9" Then

I do not believe that I am comparing values. Only saying if the cell changes then clear other cells.
 
Upvote 0
Fluff is right. Your code is not doing what you think it is:
Code:
If Target = Range("D133") Then
The line above does NOT check to see if cell D133 was updated. It checks to see whether the cell that was updated has the same value that is in cell D133!

The ".Value" is implied, so what your code really says is this:
Code:
If Target.Value = Range("D133").Value Then

If you want to see if cell D133 was updated, you need to either use the method that he showed, or do something like this:
Code:
If Not Intersect(Target, Range("D133")) is Nothing Then
 
Last edited:
Upvote 0
If you put Fluff in D9 & then change D24 to Fluff
D11, 13, 15 & 17 will get cleared using your code.
 
Upvote 0
Fluff is right. Your code is not doing what you think it is:
Code:
If Target = Range("D133") Then
The line above does NOT check to see if cell D133 was updated. It checks to see whether the cell that was updated has the same value that is in cell D133!

The ".Value" is implied, so what your code really says is this:
Code:
If Target.Value = Range("D133").Value Then

If you want to see if cell D133 was updated, you need to either use the method that he showed, or do something like this:
Code:
If Not Intersect(Target, Range("D133")) is Nothing Then

Well Fluff is USUALLY right so I will give it a shot and try it out now that I am sure my logic was wrong.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top