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

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