VBA: update date automatically when condition in two cells are met

[lunatu]

Hi,

Im using a code below to update current date automatically in a cell S when there is a specific text in cell Q. Now I would also want the value on cell R to be > 0 before the date is displayed. There should not be matter in which order you fill the cells, so there can be first specific text in cell Q and after that the value in cell R increases or the other way (first cell R increase and after that you change the text in cell Q). Any ideas how to modify the code?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [Q:Q]) Is Nothing Then
        If UCase(Target) = UCase("Negotiate") Then
            Target.Offset(, 2) = Int(Now())
        End If

    End If
  End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("Q:R")) Is Nothing Then
      If LCase(Cells(Target.Row, 17)) = "negotiate" And Cells(Target.Row, 18) > 0 Then
         Cells(Target.Row, 19).Value = Date
      End If
   End If
End Sub

 

[Marc L]

Hi, pretty the same :​

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column > 16 And .Column < 19 And .CountLarge = 1 Then
            If StrComp(Cells(.Row, 17).Text, "negociate", 1) = 0 And Cells(.Row, 18).Value2 > 0 Then
                Application.EnableEvents = False
                Cells(.Row, 19).Value = Date
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub

 

[lunatu]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("Q:R")) Is Nothing Then
      If LCase(Cells(Target.Row, 17)) = "negotiate" And Cells(Target.Row, 18) > 0 Then
         Cells(Target.Row, 19).Value = Date
      End If
   End If
End Sub

Hi, thanks!
This is working perfectly for me (i just modifyid it a bit by adding the Applications.EnableEvenst, worked smoothier) Btw, do you know if I can combine two of this, in the other code the text is "won" and the value > 0 is in cell T and I want the date to appear in cell U. Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("Q:T")) Is Nothing Then
      If LCase(Cells(Target.Row, 17)) = "won" And Cells(Target.Row, 20) > 0 Then
Application.EnableEvents = False
         Cells(Target.Row, 21).Value = Date
Application.EnableEvents = True
      End If
   End If
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("Q:R,T:T")) Is Nothing Then
      If LCase(Cells(Target.Row, 17)) = "negotiate" And Cells(Target.Row, 18) > 0 Then
         Cells(Target.Row, 19).Value = Date
      ElseIf LCase(Cells(Target.Row, 17)) = "won" And Cells(Target.Row, 20) > 0 Then
         Cells(Target.Row, 21).Value = Date
      End If
   End If
End Sub

 

[lunatu]

Hi, pretty the same :​

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column > 16 And .Column < 19 And .CountLarge = 1 Then
            If StrComp(Cells(.Row, 17).Text, "negociate", 1) = 0 And Cells(.Row, 18).Value2 > 0 Then
                Application.EnableEvents = False
                Cells(.Row, 19).Value = Date
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub

Hi, thanks for your reply! This is working also, but at least for me this is working only one way: first cell R increase and after that you change the text in cell Q. Not if the cells are filled in other order.

 

[lunatu]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("Q:R,T:T")) Is Nothing Then
      If LCase(Cells(Target.Row, 17)) = "negotiate" And Cells(Target.Row, 18) > 0 Then
         Cells(Target.Row, 19).Value = Date
      ElseIf LCase(Cells(Target.Row, 17)) = "won" And Cells(Target.Row, 20) > 0 Then
         Cells(Target.Row, 21).Value = Date
      End If
   End If
End Sub

Thanks for the fast response! I tried something like that first too but same thing than with your code; the first part (negotiation) is working as should but the second one (won) is working only other way: first value need to be > 0 and then change the text.

 

[Fluff]

With the code I posted, it does not matter whether you put won in col Q & then a number >0 in col T , or vice versa.

 

[lunatu]

With the code I posted, it does not matter whether you put won in col Q & then a number >0 in col T , or vice versa.

Yes, now it is working! Thanks a lot

 

[Fluff]

Glad we could help & thanks for the feedback.