Hi all, hope all is well with you.
I have two cells, one in which the user enters a date and the adjacen
one adds 21 days to that date to give a deadline.

What I got from one of the earlier threads is a way to conditionall
format the deadline cell so that if the deadline is less than now(
then change the font to red (I can't use the conditional forma
function as I've used all three queries already!).

The code is:

Code
-------------------

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E:E"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is < Now(): .Font.ColorIndex = 3 'red
Case Is Now(): .Font.ColorIndex = 1 'yellow
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

-------------------


However, the problem is that the formula is only ran -after- text ha
been entered! That doesn't work for me since the deadline will expir
in 21 days, not the day the text is entered

How can I run the formula on the entire column as soon as the exce
form is opened or every set time or something?

Thanks very much

--
BravoGol

-----------------------------------------------------------------------
BravoGolf's Profile: http://www.excelforum.com/member.php...fo&userid=1475
View this thread: http://www.excelforum.com/showthread.php?threadid=46881

To run every time the workbook is active place the same code in
'ThisWorkbook' using the workbook_open event. You could also place it in the
workbook_activate event.

To cover all bases you could put it in each of the open/activate/change
events. That way, no matter what happens it will get updated.

"BravoGolf" wrote:


Hi all, hope all is well with you.
I have two cells, one in which the user enters a date and the adjacent
one adds 21 days to that date to give a deadline.

What I got from one of the earlier threads is a way to conditionally
format the deadline cell so that if the deadline is less than now()
then change the font to red (I can't use the conditional format
function as I've used all three queries already!).

The code is:

Code:
--------------------

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E:E"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is < Now(): .Font.ColorIndex = 3 'red
Case Is Now(): .Font.ColorIndex = 1 'yellow
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--------------------


However, the problem is that the formula is only ran -after- text has
been entered! That doesn't work for me since the deadline will expire
in 21 days, not the day the text is entered

How can I run the formula on the entire column as soon as the excel
form is opened or every set time or something?

Thanks very much!


--
BravoGolf


------------------------------------------------------------------------
BravoGolf's Profile: http://www.excelforum.com/member.php...o&userid=14755
View this thread: http://www.excelforum.com/showthread...hreadid=468812

the VBA answer is in the workbook_Open Event but if you are more comforable
in Conditional formating it will be a better answer.

Often you can workaround the 3 condition limit for conditional formatting by
adding a new column adjacent to the conditional column. Add up to 3
conditions to this column, based on the value in your column change this
cells background color to red, add "OVERDUE" or whatever.
using this with a column before and after you can actually have an
understandable 9 condition format for your report.


"BravoGolf" wrote:


Hi all, hope all is well with you.
I have two cells, one in which the user enters a date and the adjacent
one adds 21 days to that date to give a deadline.

What I got from one of the earlier threads is a way to conditionally
format the deadline cell so that if the deadline is less than now()
then change the font to red (I can't use the conditional format
function as I've used all three queries already!).

The code is:

Code:
--------------------

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E:E"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is < Now(): .Font.ColorIndex = 3 'red
Case Is Now(): .Font.ColorIndex = 1 'yellow
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--------------------


However, the problem is that the formula is only ran -after- text has
been entered! That doesn't work for me since the deadline will expire
in 21 days, not the day the text is entered

How can I run the formula on the entire column as soon as the excel
form is opened or every set time or something?

Thanks very much!


--
BravoGolf


------------------------------------------------------------------------
BravoGolf's Profile: http://www.excelforum.com/member.php...o&userid=14755
View this thread: http://www.excelforum.com/showthread...hreadid=468812

Hey JNW, thanks for your reply.
Forgive me, but do you mean I do the following?

Code
-------------------

Private Sub workbook_activate()
GoTo Worksheet_Change
End Sub


Private Sub Workbook_Open()
GoTo Worksheet_Change
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E:E"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is < Now(): .Font.ColorIndex = 3 'red
Case Is Now(): .Font.ColorIndex = 1 'yellow
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

-------------------


I think my syntaxt might be wrong :(

Vacation's Over, thank you for your reply, but unfortunately I canno
use any more conditional formats

--
BravoGol

-----------------------------------------------------------------------
BravoGolf's Profile: http://www.excelforum.com/member.php...fo&userid=1475
View this thread: http://www.excelforum.com/showthread.php?threadid=46881

That is a lot more concise than what I was thinking. Looks good.

"BravoGolf" wrote:


Hey JNW, thanks for your reply.
Forgive me, but do you mean I do the following?

Code:
--------------------

Private Sub workbook_activate()
GoTo Worksheet_Change
End Sub


Private Sub Workbook_Open()
GoTo Worksheet_Change
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E:E"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is < Now(): .Font.ColorIndex = 3 'red
Case Is Now(): .Font.ColorIndex = 1 'yellow
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--------------------


I think my syntaxt might be wrong :(

Vacation's Over, thank you for your reply, but unfortunately I cannot
use any more conditional formats.


--
BravoGolf


------------------------------------------------------------------------
BravoGolf's Profile: http://www.excelforum.com/member.php...o&userid=14755
View this thread: http://www.excelforum.com/showthread...hreadid=468812

Did a quick on the forum here and found the following website:
http://www.cpearson.com/excel/ontime.htm

You could use some of the functions found there to have your sub run every
hour or the frequency that you desire.

"BravoGolf" wrote:


Hey JNW, thanks for your reply.
Forgive me, but do you mean I do the following?

Code:
--------------------

Private Sub workbook_activate()
GoTo Worksheet_Change
End Sub


Private Sub Workbook_Open()
GoTo Worksheet_Change
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E:E"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is < Now(): .Font.ColorIndex = 3 'red
Case Is Now(): .Font.ColorIndex = 1 'yellow
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--------------------


I think my syntaxt might be wrong :(

Vacation's Over, thank you for your reply, but unfortunately I cannot
use any more conditional formats.


--
BravoGolf


------------------------------------------------------------------------
BravoGolf's Profile: http://www.excelforum.com/member.php...o&userid=14755
View this thread: http://www.excelforum.com/showthread...hreadid=468812