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