E-Mail generation when cell value equals x

[BaggieDan]

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

 

[JP]

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