Question on conditional formatting & email macro

[Guest]

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

 

[Ron de Bruin]

Hi Max

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

 

[Guest]

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]

Hi Max

In VBA useDate

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

 

[Guest]

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]

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

 

[Guest]

That works Ron

Thanks!
Max

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

 

[Guest]

Ron,

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

Max

 

[Dave Peterson]

How about changing one line and testing it?

If Not Intersect(me.range("G:G"), rng) Is Nothing Then

But I'm not sure which cell should be compared to the date?

There could be multiple dependent cells in column G???

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim myGRng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
Set myGRng = Intersect(Me.Range("G:G"), rng)
If myGRng Is Nothing Then
'do nothing
Else
If myGRng.Cells(1).Value = Date Then
MsgBox "Then Mail_with_outlook"
end if
End If
End If
EndMacro:
End Sub

 

[Ron de Bruin]

Hi Max

I don't think you want to send a mail each time a formula cell is changed in Column G ?
Play with Dave's suggestion and if this is not working for you post back