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.

 

[Patriot2879]

Hi thanks. I have x2 private subs with the same name though. How would I add to the one I already have?

 

[Fluff]

What's the code you already have?

 

[Patriot2879]

hi the other code is

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, Rw As Range

  Set Changed = Intersect(Target, Union(Range("H:H"), Range("H:H")))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each Rw In Changed.Rows
      Cells(Rw.Row, "Q").Value = Environ("Username")
      Cells(Rw.Row, "R").Value = Now
      Cells(Rw.Row, "R").NumberFormat = "dd/mm/yyyy hh:mm"
    Next Rw
    Application.EnableEvents = True
  End If
  
End Sub

 

[Fluff]

try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rw As Range
   
   If Not Intersect(Target, Range("H:H")) Is Nothing Then
      Application.EnableEvents = False
      For Each Rw In Target.Rows
         Cells(Rw.Row, "Q").Value = Environ("Username")
         Cells(Rw.Row, "R").Value = Now
         Cells(Rw.Row, "R").NumberFormat = "dd/mm/yyyy hh:mm"
      Next Rw
   ElseIf 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
   Application.EnableEvents = True
End Sub

 

[Patriot2879]

thank you I will try that, please can you advise for future reference if I come across this again and I need to add extra to this sub how will I do it?

 

[Fluff]

In much the same way as i did above.

 

[Patriot2879]

hi thank you, I have just changed the code a little by extending the range but it came back with an error, it didnt like the s5:v5 range as want it to check 5 cells as a number will be in one of those, can you help please?

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rw As Range
   
   If Not Intersect(Target, Range("H:H")) Is Nothing Then
      Application.EnableEvents = False
      For Each Rw In Target.Rows
         Cells(Rw.Row, "Q").Value = Environ("Username")
         Cells(Rw.Row, "R").Value = Now
         Cells(Rw.Row, "R").NumberFormat = "dd/mm/yyyy hh:mm"
      Next Rw
   ElseIf Target.Address(0, 0) = "AA5" Then
      If LCase(Target.Value) = "cancel all" Then
         Range("S5:V5").Clear
      ElseIf LCase(Target.Value) = "cancel 1" Then
         Range("S5:V5").Value = Range("S5:V5").Value - 1
      End If
   End If
   Application.EnableEvents = True
End Sub

 

[Fluff]

Is this the last change that you are going to request, before I re-write it again?

 

[Patriot2879]

yes definitely sorry, you are very helpful, and many thanks

 

[Fluff]

Do you want to change the value in all cells from S5 to V5?