Hi! I’m trying to get my macro to run when a column of numbers (based on formulas) contains a specific number. Basically, I want VBA to read the number not the formula in this column.
I am working on a spreadsheet of material that will expire at various points in time. Currently, I have a column containing “days until expiration” that are run on their expiration date minus today.
ex.) =H5-TODAY()
I want my macro to send an email to a few of my coworkers (I already have this code) when we are 180 days from expiration, but VBA isn’t reading the number in the cell, it only triggers when I manually enter 180 into the cell.
Any thoughts on how to fix this?
Here’s the code:
Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("I2:I99"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value = 180 Then
Call Mail_small_Text_Outlook
End If
End Sub
I am working on a spreadsheet of material that will expire at various points in time. Currently, I have a column containing “days until expiration” that are run on their expiration date minus today.
ex.) =H5-TODAY()
I want my macro to send an email to a few of my coworkers (I already have this code) when we are 180 days from expiration, but VBA isn’t reading the number in the cell, it only triggers when I manually enter 180 into the cell.
Any thoughts on how to fix this?
Here’s the code:
Dim xRg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("I2:I99"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value = 180 Then
Call Mail_small_Text_Outlook
End If
End Sub