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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,036
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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,475
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,036
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
35,475
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,036
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,036
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,036
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,036
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,036
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
35,475
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,089,169
Messages
5,406,585
Members
403,097
Latest member
danielcarrington

This Week's Hot Topics

Top