Pro-actively run formula

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
>
>

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
>
>