E-Mail generation when cell value equals x
 

Hi All,

I have the following code from Ron db most excellent website. It generates
an e-mail when the value in one cell equals 1. What I would like is for this
to work in a range of cells.

The spreadsheet is used as a sickness log which has, amoungst others, 3
columns, due date, E-Mail, Actual Date (G12, H12 and I12 respectively). When
a due date appears in G12 the value of H12 changes from 0 to 1 and an e-mail
is generated with the code below. But when the user completes an actual date
the next due date (G13) is generated and the value of H12 changes back to 0
and the value of H13 changes from 0 to 1. It is this that I am using to
trigger the e-mail but as you can see from the code below it is only looking
at one cell H12.

How can I get this macro to work on all cells H12 to H67?

I hope I have explained myself clearly!

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("H12"), rng) Is Nothing Then

If Range("H12").Value = 1 Then EMailReminder

End If
End If
EndMacro:
End Sub

E-Mail generation when cell value equals x
 

You need to update the following line

If Not Intersect(Range("H12"), rng) Is Nothing Then

to cover the range you want to monitor. So if you wanted to check
H12:H100, it would read

If Not Intersect(Range("H12:H100"), rng) Is Nothing Then

--JP


On Oct 15, 12:14*pm, BaggieDan
wrote:
Hi All,

I have the following code from Ron db most excellent website. *It generates
an e-mail when the value in one cell equals 1. *What I would like is for this
to work in a range of cells.

The spreadsheet is used as a sickness log which has, amoungst others, 3
columns, due date, E-Mail, Actual Date (G12, H12 and I12 respectively). *When
a due date appears in G12 the value of H12 changes from 0 to 1 and an e-mail
is generated with the code below. *But when the user completes an actual date
the next due date (G13) is generated and the value of H12 changes back to 0
and the value of H13 changes from 0 to 1. *It is this that I am using to
trigger the e-mail but as you can see from the code below it is only looking
at one cell H12.

How can I get this macro to work on all cells H12 to H67?

I hope I have explained myself clearly!

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
* * Dim rng As Range
* * If Target.Cells.Count 1 Then Exit Sub
* * On Error GoTo EndMacro
* * If Not Target.HasFormula Then
* * * * Set rng = Target.Dependents
* * * * If Not Intersect(Range("H12"), rng) Is Nothing Then

* * * * * * If Range("H12").Value = 1 Then EMailReminder

End If
End If
EndMacro:
End Sub