Macro with Message Box for Date Greater Than Today

[rhmkrmi]

Hi there, I need a VBA to be used in Excel to check a couple of columns (F:F, H:H, N:N) to see if any cell in those columns contain a date that is greater than today and pop up a message. Could you please help?

Thank you.

 

[Fazza]

If Application.WorksheetFunction.Max(Columns("F"), Columns("H"), Columns("N")) > Date Then MsgBox "a message"

 

[rhmkrmi]

Thanks Fazza.

I am using a text format for my dates (TEXT(F10,"dd-mmm-yyyy")).
How can the code be changed to pick up this format as well as a true date format?

Thank you.

 

[Fazza]

That isn't a text format: it is text. Not a date as far as Excel is concerned. The code doesn't need changing.

The formula refers to column F so column F presumably really does contain a date. And again the code should be fine.

The code is independent of formats. For example, enter 44444 in a cell. It can be formatted (CTRL-1) as number like 44,444 or a date like 5-Sep-21

However using a TEXT formula returns text.

 

[Fazza]

PS,

If you have text instead of dates here are some ways to convert them to numbers so Excel handles them as dates,
=value(a1)
=a1*1
=a1+0

 

[rhmkrmi]

Thanks a lot for all the tips. I will try them.

In the meantime, can I also ask how I can use the code that you provided in Sheet1 but reference it to the same columns in Sheet2?

In Sheet2 I have dates but in Sheet1 I have converted them to text because that especial format is required.

Thanks again.

 

[Fazza]

you can add the reference to Sheet2

if Sheet2 is the code name

If Application.WorksheetFunction.Max(Sheet2.Columns("F"), Sheet2.Columns("H"), Sheet2.Columns("N")) > Date Then MsgBox "a message"

 

[rhmkrmi]

Thank you.

I am using this but it pops up a message when I enter a future date in any field!

Private Sub Worksheet_Change(ByVal Target As Range)


If Application.WorksheetFunction.Max(Columns("F"), Columns("H"), Columns("N")) > Date Then MsgBox "Please check your dates"


End Sub

What am I doing wrong?

Thank you.

 

[Fazza]

I can't tell. I don't understand exactly what you've done.

What do you think it is?

 

[Fazza]

Maybe there is already - before the data entry that triggers the macro - a future date in one of the columns of interest?