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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
941
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
32,057
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
941
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
32,057
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
941
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
941
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
941
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
941
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
941
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
32,057
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,081,680
Messages
5,360,512
Members
400,589
Latest member
Mikealphatangoc

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top