Vba code for an if and statements

[ifu06416]

Hello,

I have a table where users enter data. The final entry, in col M, is their name.

I would like to create an VBA code that will display a msg box if the user enters their name in col M without having entered a date in col J.

I've written the following if function which seems to do the job but I don't know how to change it into VBA

=IF(AND(M12="",J12=""),"",IF(AND(M12="",J12<>""),"",IF(AND(J12<>"",M12<>""),"","DATE NEEDED")))

Any help would be appreciated.

John.

 

[baitmaster]

Your formula will probably simplify to
=IF(AND(J12="",M12<>""),"date needed","")
In VBA this could be

if range("J12") = "" and range("M12") <> "" then msgbox "date needed"

But I'd want to see more of your code so I can see it in context - how it's called, whether other ranges are affected etc., because this single line isn't complete

 

[Special-K99]

Something like

If Cells(12,13)<>"" and Cells(12,10)="" Then
    MsgBox "Date Needed"
End If

 

[ifu06416]

Hi Baitmaster,

There isn't actually any further code, this will be the only macro in use.

I've applied your code in the following way

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("J12") = "" And Range("M12") <> "" Then
MsgBox "date needed"
End If
End Sub

It works great for row 12 but how would I edit this code so that it applies to, say rows 12 down to row 5000?

Thanks for the reply.

John.

 

[baitmaster]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDates As Range: Set rngDates = Range("J12:J5000")
Dim rngNames As Range: Set rngNames = Range("M12:M5000")
If Not Intersect(Target, Union(rngDates, rngNames)) Is Nothing Then
    If Intersect(rngDates, Target.EntireRow) = "" And Intersect(rngNames, Target.EntireRow) <> "" Then MsgBox "date needed: row " & Target.Row
End If
End Sub

 

[ifu06416]

Thank you, that works great.