RUN MACRO WHEN CELL IN COLUMN CHANGES VALUE
I am trying to run a macro when any cell in column 14 is less than or
equal to Today. Can anyone help? |
RUN MACRO WHEN CELL IN COLUMN CHANGES VALUE
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 - - - - - - - - |
RUN MACRO WHEN CELL IN COLUMN CHANGES VALUE
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? |
RUN MACRO WHEN CELL IN COLUMN CHANGES VALUE
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? |
RUN MACRO WHEN CELL IN COLUMN CHANGES VALUE
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 |
All times are GMT +1. The time now is 09:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com