Multiple If statement

[Corleone]

Hopefully someone can help

spreadsheet contains 300 rows of data

In some cases the contractor who fills in the spreadsheet will fill in column j with "y" TO Indicate a particular task is done, but will then forget to put in the Actual completion dates

so I would if possible like the following to happen..........

If column J contains "Y" and the Cell in column H or I contain "TBD" - I would like
the Cells in H & I to be populated with "Date Required"

If possible i would like this to be as a worksheet event as the relevant cells already contain formulas/validation

Thanks

 

[SteveO59L]

How is TBD entered in col H ?

 

[Corleone]

From a dropdown list using data validation



cheers

 

[SteveO59L]

Then you would need a coded solution, as the data validation action would overwrite a formula

 

[Corleone]

Then you would need a coded solution, as the data validation action would overwrite a formula

Can anyone offer a coded solution?



forgot to mention ""Date Required" is one of the options in the data validation drop down

 

[SteveO59L]

Something along the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 And Target.Value = "y" Then
Target.Offset(0, -2).Value = "Date Required"
Target.Offset(0, -1).Value = "Date Required"
End If


End Sub

 

[lenze]

Not really sure what you need, but this should get you started!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If UCase(Target) = "Y" Then
    With Cells(Target.Row, 8).Resize(1, 2)
       .Value = "Date Required"
    End With
End If
End Sub

lenze

 

[Corleone]

Not really sure what you need, but this should get you started!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If UCase(Target) = "Y" Then
    With Cells(Target.Row, 8).Resize(1, 2)
       .Value = "Date Required"
    End With
End If
End Sub

lenze

Thanks Guys for the help - using the above code i just need on other tweak!
, if either of the cells in column H or I already have dates in them, i would like them to remain unchanged. and only for it to change the cells with "TBD" in them

thanks Again

 

[lenze]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If UCase(Target) = "Y" Then
    If Not IsDate(Cells(Target.Row, 8)) Then Cells(Target.Row, 8) = "Date Required"
    If Not IsDate(Cells(Target.Row, 9)) Then Cells(Target.Row, 9) = "Date Required"
End If
End Sub

lenze

 

[Corleone]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If UCase(Target) = "Y" Then
    If Not IsDate(Cells(Target.Row, 8)) Then Cells(Target.Row, 8) = "Date Required"
    If Not IsDate(Cells(Target.Row, 9)) Then Cells(Target.Row, 9) = "Date Required"
End If
End Sub

lenze

Many Thanks Lenze, Just what i was looking for