VBA for validating data

[Godwin117]

I have this excel document that dates are entered in cells F12 and G12 to the last row, data validation works when someone enters the data, but the dates don't change. I would like to know if there is a vba I could use to validate those dates and have a message box that shows where the errors are located.

 

[Joe4]

I am a bit confused by your question.
I think we need more information about your process.
What exactly is causing these errors?

 

[LazyBug]

I have this excel document that dates are entered in cells F12 and G12 to the last row, data validation works when someone enters the data, but the dates don't change. I would like to know if there is a vba I could use to validate those dates and have a message box that shows where the errors are located.

Hello!

You need specific validation rules, list all the conditions. What cells or ranges data affect on what ?

 

[Godwin117]

Hello!

You need specific validation rules, list all the conditions. What cells or ranges data affect on what ?

For Column "G12: last row of data in column A", if the date is < today, then the message pops up showing what row the invalid date is located.
For column "F12: last row of data in column A", if the date is > today, then the message pops up showing what row the invalid date is located.
For either column, if date is = today, then message pops up asking the user to verify that date is correct.

 

[LazyBug]

What event should pop up message / messages? What should be result of closing these messages?
What rows contains dates?
What column is the longest?

Would you consider variant with conditional formatting?

 

[Godwin117]

What event should pop up message / messages? What should be result of closing these messages?
What rows contains dates?
What column is the longest?

Would you consider variant with conditional formatting?

If the date is invalid, a message box should pop up with an ok button.
Closing the messages end the macro.
The dates are in variable rows, but if there arent dates then it is blank.
Column A is the longest and has every row filled with information.
I currently have a conditional formatting, I'm trying to limit the size of the file. It's about 105MB as of now.

 

[LazyBug]

If the date is invalid, a message box should pop up with an ok button.
Closing the messages end the macro

If the document already has incorrect data, do you want msgbox pop up once at the file opening?

The dates are in variable rows, but if there arent dates then it is blank.
Column A is the longest and has every row filled with information.

Got it. G is the right column?

I currently have a conditional formatting, I'm trying to limit the size of the file. It's about 105MB as of now.

It could be a problem soon. Doesn't cleaning up a lot of stuff help?

 

[Godwin117]

Yes that is actually a better idea, thank you. Then probably verify before saving the workbook.
Yes G is the required column as well as F, just different validations.
It really does, finally found out to get a macro to automatically put the formulas in that will he used. Having 50 sheets doesnt help either lol.

 

[LazyBug]

I'm allowed myself to make two buttons instead of one. It's more convenient, i think. Paste this to ThisWorkbook module.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
On Error Resume Next

Dim iAF As Variant, iAG As Variant, iR&, lR&, d As Date: d = Date

lR = Cells(Rows.Count, 1).End(xlUp).Row
iAF = Range("F12:F" & lR).Value
    For iR = 1 To UBound(iAF)
            If iAF(iR, 1) > d Then
                Dim m As Integer, mp$, mt$
                mt = "Next?"
                mp = "Need to check Date in F" & iR& + 11
                    m = MsgBox(mp, 4 + 32, mt)
                        If m = 6 Then
                        Else
                            Exit Sub
                        End If
            End If
    Next
    
iAG = Range("G12:G" & lR).Value
    For iR = 1 To UBound(iAF)
            If iAG(iR, 1) < d Then
                mt = "Next?"
                mp = "Need to check Date in G" & iR& + 11
                    m = MsgBox(mp, 4 + 32, mt)
                        If m = 6 Then
                        Else
                            Exit Sub
                        End If
            End If
    Next

Application.ScreenUpdating = True
End Sub

 

[LazyBug]

Make shure F and G column cells' formate is Date only (without time).