Help with VBA

[xPrymaL]

Just trying to create a macro that runs when I open my workbook. I need it to check a date in a column(h) and return a pop up if the date is today or has gone past already.

I then need to check a column in the same workbook(i) to see if there has been another date entered and if there has to not display the first pop up.

---

I've managed to create the macro to make the pop up appear if the date has gone past, but I cannot get it to check the next column and not display the pop up if I have entered another date.

I've attempted to use an ElseIf statement, but I have probably used it in the wrong way.

---

I'm new to using VBA so any help would be greatly appreciated

---

Current Code:

Private Sub Autpen()
'Sets cell as Range
Dim cell As Range
'Checks Date in cell and gives pop up on Date past call date
For Each cell In Range("h3, h100")
If IsDate(cell.Value) Then
If cell.Value <= Now() Then
MsgBox "Call Required:" & cbCrLf & cell.Value & "; S/O# " & cell.Offset(0, -7).Value
End If
End If
Next cell
End Sub

 

[eduzs]

For Each cell In Range("h3, h100")
This not should be For Each cell In Range("h3:h100") ??

 

[Michael M]

This assumes there is a date in col I

Private Sub Auto_Open()
'Sets cell as Range
Dim cell As Range
'Checks Date in cell and gives pop up on Date past call date
For Each cell In Range("H3:H100")
If IsDate(cell.Value) And cell.Value <= Date And Not IsDate(cell.Offset(, 1)) Then
MsgBox "Call Required:" & vbCrLf & cell.Value & "; S/O# " & cell.Offset(, -7).Value
End If
Next cell
End Sub

 

[xPrymaL]

You are certainly correct, I had a bit of a brain fart and miss-typed.

Thanks.

 

[Michael M]

glad to help...

 

[xPrymaL]

I don't know if it's what I've got written in my columns, but it doesn't seem to work for me.

I've got dates in column H, and a date in column I, but when the date in column I is set to an earlier date than today it doesn't give me a pop up on opening.

any ideas?

 

[Michael M]

You didn't say EARLIER date in your first post, you said ANOTHER date!!
Try this

Private Sub Auto_Open()
'Sets cell as Range
Dim cell As Range
'Checks Date in cell and gives pop up on Date past call date
For Each cell In Range("H3:H100")
If IsDate(cell.Value) And cell.Value <= Date And cell.Offset(, 1).Value <= Date Then
MsgBox "Call Required:" & vbCrLf & cell.Value & "; S/O# " & cell.Offset(, -7).Value
End If
Next cell
End Sub

 

[xPrymaL]

My bad! But it works now!

So thanks!!

 

[Michael M]