How To clear cell if cell another cell has "No.

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,021
Hi hope you can help, I have a cell X2 where you have a dropdown box with yes or no, if no is selected I want this to clear the cell in U2, how do this can you help me please? I am still fairly new to this and still learning.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,929
Office Version
365
Platform
Windows
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "X2" And LCase(Target.Value) = "no" Then Range("U2").Clear
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,021
Thank you for the help. Just one more question. Is there any wsy to deduct the cell u2 by -1 if I add text in cell x2 saying 'cancel 1' so if 'cancel 1' is in x2 and the number 8 is in cell u2 i want the number to go down to 7. Hope this is possible? Your knowledge and help is amazing
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,929
Office Version
365
Platform
Windows
Try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "X2" Then
      If LCase(Target.Value) = "no" Then
         Range("U2").Clear
      ElseIf LCase(Target.Value) = "cancel 1" Then
         Range("U2").Value = Range("U2").Value - 1
      End If
   End If
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,021
Thank you i will try this tomorrow morning thanks for the help so much appreciated. I have been trying to work this out for ages
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,021
Hi just tried the code thankyou I have amended to the below, but in my worksheet I already have a
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ad the worksheet wont let me add another with he same private sub name, please can you advice?

Code:
Private Sub Worksheet_Change(ByVal Target As Range) 

If Target.Address(0, 0) = "AA5" Then
If LCase(Target.Value) = "Cancel All" Then
Range("T5").Clear
ElseIf LCase(Target.Value) = "cancel 1" Then
Range("T5").Value = Range("T5").Value - 1
End If
End If
End Sub
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,021
hiya what I mean about the above is that I have already a different code with the same private sub name and it wont let me add the new coding with the same private sub, what can I do please?
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,021
hiya I just tried the code in a blank sheet but I couldn't get it to work, I had 'cancel 1' in cell AA5 and a number 4 in cell T5 but it didn't subtract down to 3, hope you can still help please
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,021
hi I got the code to work :) on new sheet but chnged the private sub to Private Sub Worksheet_Change1(ByVal Target As Range)
but when I add the 1 it didn't work
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,929
Office Version
365
Platform
Windows
Try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) = "AA5" Then
   If LCase(Target.Value) = "[COLOR=#0000ff]cancel all[/COLOR]" Then
      Range("T5").Clear
   ElseIf LCase(Target.Value) = "cancel 1" Then
      Range("T5").Value = Range("T5").Value - 1
   End If
End If
End Sub
Do not change the name of the sub, otherwise it will never run.
 

Forum statistics

Threads
1,086,039
Messages
5,387,432
Members
402,063
Latest member
Cordeiropolis

Some videos you may like

This Week's Hot Topics

Top