Ron,

What if, instead of "G3" I want any cell in G? I tried "G$" but nothing
happened

Max



"Max" wrote:

That works Ron

Thanks!
Max

"Ron de Bruin" wrote:

With a formula in G3 this is working (I use a msgbox to test)
When G3 = todays date you see the msgbox

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("G3"), rng) Is Nothing Then
If Range("G3").Value = Date Then MsgBox "Then Mail_with_outlook"
End If
End If
EndMacro:
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Max" wrote in message ...
Hi Ron,

I changed "= Today()" to "= Date" but it still sends email regardless of the
date.

I'll work on it some more.
Thank you
Max


"Ron de Bruin" wrote:

Hi Max

In VBA useDate

If Range("G3").Value = Date Then ................

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Max" wrote in message ...
Yes, however when I run the macro it sends email regardless of the date. I'm
new to this so I'm probably just missing something

Heres the code I'm using

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("G3"), rng) Is Nothing Then
If Range("G3").Value = Today() Then Mail_with_outlook
End If
End If
EndMacro:
End Sub


"Ron de Bruin" wrote:

Hi Max

Have you try this
http://www.rondebruin.nl/mail/change.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Max" wrote in message ...
Hi,

I am working on a spreadsheet that is used to track project progress.
A start date is manually entered and the spreadsheet automatically fills in
the expected start and finish dates for the various steps in the project. I
need a cell (that contains a date) to turn orange when the date is 10 days or
less from todays date. I already have it conditionally formatted to turn red
when todays date = the cell date.

Also, I have been trying to figure out how to make the excel email the
responsible project leader when the due date is less than 5 days from todays
date but I have had difficulty figuring out the Ron de Bruin macro.

I'm running Excel 2003 SP2.
Any help is appreciated.

Thanks
Max