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

K

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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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
Joe4 said:
Please outline for us a specific example of one that is not working correctly.
What are you updating?
What happens?
What should happen?
Click to expand...

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
Fluff said:
You are comparing the values in the cells not their address.
Try using
Code: 
If Target.Address(0, 0) = "D9" Then
Click to expand...

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
Joe4 said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA not working!
Replies
2
Views
197
jsweeney147
J
D
  • Solved
finding changes and looking back
2
Replies
10
Views
411
felixstraube
F
A
  • Solved
Autonumbering in cell based on last row in column
Replies
2
Views
70
abdo meghari
A
D
  • Question Question
How to Capitalise the First letter in each sentence and add to existing VBA Code.
2
Replies
16
Views
1K
Detectiveclem
D
B
  • Question Question
Calculating Annualized returns of 1 year, 3 years, 5 years, and 10 years from the data of 132 months/ 11 Years
Replies
0
Views
598
Brandon_333
B

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

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