I am trying to run a macro when any cell in column 14 is less than or
equal to Today. Can anyone help?

On Apr 8, 2:06*pm, wrote:
I am trying to run a macro when any cell in column 14 is less than or
equal to Today. Can anyone help?

There are a bunch of different ways to do this but this is a really
basic macro that works.

You might want to associate it with a SelectionChange event as opposed
to a Change event because this macro will continually run every time
the worksheet is re-calculated and there is a date greater than today
in column 14.

- - - - - - - -

Private Sub Worksheet_Change(ByVal Target As Range)

For i = 1 To 65000
If IsDate(Cells(i, 14)) And Cells(i, 14) Now Then
MsgBox "Macro!"
Exit Sub
End If
Next i

End Sub

- - - - - - - -

maybe

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("N:N")) Is Nothing Then
If IsDate(Target) And Target.Value <= Date Then
'do something
End If
End If
End Sub

worksheet code

Mike

" wrote:

I am trying to run a macro when any cell in column 14 is less than or
equal to Today. Can anyone help?

On Apr 8, 12:18*pm, "Brian B." wrote:
On Apr 8, 2:06*pm, wrote:

I am trying to run a macro when any cell in column 14 is less than or
equal to Today. Can anyone help?

There are a bunch of different ways to do this but this is a really
basic macro that works.

You might want to associate it with a SelectionChange event as opposed
to a Change event because this macro will continually run every time
the worksheet is re-calculated and there is a date greater than today
in column 14.

- - - - - - - -

Private Sub Worksheet_Change(ByVal Target As Range)

For i = 1 To 65000
If IsDate(Cells(i, 14)) And Cells(i, 14) Now Then
MsgBox "Macro!"
Exit Sub
End If
Next i

End Sub

- - - - - - - -

Thank you!

One more question - There could be a value of "Received" in the same
column and I do not want to the macro to run if that is the case. Can
I write that in?

Thank you!

One more question - There could be a value of "Received" in the same
column and I do not want to the macro to run if that is the case. Can
I write that in?- Hide quoted text -

- Show quoted text -

Of course ... w/ Excel anything is possible (and there are normally
multiple way to do things)! :)

Overall I like Mike's code better than my previous post b/c it will
only trigger the macro when the date is actually entered (not when a
calculation occurs). Below is code where if "Received" appears as the
value of a cell in Column N the macro does not run. There is a bunch
more you can do where if the value of the cell to the left or right of
the cell is equal to "Received" then to not trigger the macro (but in
your post you said same column).

- - - - - - - - -

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Txt As String
Dim x As Integer

Txt = "Received"

If Not Intersect(Target, Range("N:N")) Is Nothing Then

x = Application.WorksheetFunction.CountIf(Columns("N:N "), Txt)
If x 0 Then Exit Sub

If IsDate(Target) And Target.Value <= Now Then
'do something
MsgBox "Macro!"
'do something
End If
End If

End Sub

- - - - - - - - -

Below is code where if the cell in column M has "Received" the macro
does not run.

- - - - - - - - -

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Txt As String
Txt = "Received"

If Not Intersect(Target, Range("N:N")) Is Nothing Then

If Target.Offset(0, -1).Value = Txt Then Exit Sub

If IsDate(Target) And Target.Value <= Now Then
'do something
MsgBox "Macro!"
'do something
End If
End If

End Sub


- - - - - - - - -

-Brian