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 I want it to look for the number in s5, t5, u5, v5 as it will be in one of these cells, then update the cell which has the number in, thank you

 

[Fluff]

OK, 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("S5:V5").Clear
      ElseIf LCase(Target.Value) = "cancel 1" Then
         With Range("S5:V5")
            .Value = Evaluate("if(" & .Address & "<>""""," & .Address & "-1,"""")")
         End With
      End If
   End If
   Application.EnableEvents = True
End Sub

 

[Patriot2879]

Thank you for the help. What does the evaluate and address line do please? I tried the s5:v5 before but didn't work but i see you added a new line. Thank you again

 

[Fluff]

Glad to help & thanks for the feedback.

That line simply checks if the cells in the range have a value & if so subtract 1 from them, otherwise returns ""

 

[Patriot2879]

that is great thankyou for the education and help much appreciated

 

[Patriot2879]

hi good morning, sorry just one more question, I have tried duplicating the code to add another cell (the one below) please see below what I have done, but I am getting an error, please can you advise what I have done wrong? thank you, I did say no more questions from me thinking I would be okay

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
         With Range("S5:V5")
            .Value = Evaluate("if(" & .Address & "<>""""," & .Address & "-1,"""")")
            Next Rw
             ElseIf Target.Address(0, 0) = "AA6" Then
      If LCase(Target.Value) = "cancel all" Then
         Range("S6:V6").Clear
      ElseIf LCase(Target.Value) = "cancel 1" Then
         With Range("S6:V6")
            .Value = Evaluate("if(" & .Address & "<>""""," & .Address & "-1,"""")")
         End With
      End If
   End If
   Application.EnableEvents = True
End Sub

 

[Patriot2879]

HI is is the new bit I added trying to copy what was for the previous cells but just changing the number to the next cell below ie AA6 and S6:V6 thought it would be simple to add the new lines but getting the error

            Next Rw
             ElseIf Target.Address(0, 0) = "AA6" Then
      If LCase(Target.Value) = "cancel all" Then
         Range("S6:V6").Clear
      ElseIf LCase(Target.Value) = "cancel 1" Then
         With Range("S6:V6")
            .Value = Evaluate("if(" & .Address & "<>""""," & .Address & "-1,"""")")

 

[Patriot2879]

hello, hope you can help , you have been great with all the advice already, I did think this would be easy to do and just paste previous code with the section and paste underneath and just change the cells range for example aa5 t aa6 and s5:v5 to s6:v6, do I need to break it up somehow?

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rw As Range
   
   Application.EnableEvents = False
   If Not Intersect(Target, Range("H:H")) Is Nothing Then
      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 Not Intersect(Target, Range("AA5:AA6")) Is Nothing Then
      If LCase(Target.Value) = "cancel all" Then
         Range("S" & Target.Row).Resize(, 4).Clear
      ElseIf LCase(Target.Value) = "cancel 1" Then
         With Range("S" & Target.Row).Resize(, 4)
            .Value = Evaluate("if(" & .Address & "<>""""," & .Address & "-1,"""")")
         End With
      End If
   End If
   Application.EnableEvents = True
End Sub

 

[Patriot2879]

Thankyou again that works great