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

[Patriot2879]

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]

How about

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]

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]

Try

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]

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]

Hi just tried the code thankyou I have amended to the below, but in my worksheet I already have a

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?

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]

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]

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]

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

 

[Fluff]

Try

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.