| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on conditional formatting & email macro
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 Max wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question on conditional formatting & email macro
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Max" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting with a prompt and new email message | Excel Discussion (Misc queries) | |||
| Conditional Formatting Question | Excel Worksheet Functions | |||
| Conditional Formatting Question | Excel Discussion (Misc queries) | |||
| Conditional formatting - copy & paste to an email | Excel Discussion (Misc queries) | |||
| Excel Macro Question about Conditional Formatting | New Users to Excel | |||
Linear Mode
