I would make an array out of the weeks, like for example weeks = Array(9.14.2004,9.21.2004,9.28.2004 ...). If you wish to put the date for today in A26 write: Range("A26").Value = Date. And if you wish to put a message box telling you what date is today write: MsgBox "The date for today is " & Date. I would prefer to use the Workbook_Open() procedure than use the Worksheet_Activate(). It opens immediately when you open the workbook. So just a little improvement on your work would look like this:

Public Sub Workbook_Open()

Dim weeks As Date
Dim krisScore As Integer
Dim oppScore As Integer
Dim currentWeek As Date

weeks = Array(9.14.2004,9.21.2004,9.28.2004 ...)

Range("A26").Value = Date
Call CompareValues
End Sub

Public Sub CompareValues()
MsgBox Date
' I don't know what you really what to accomplish with this procedure.
' Call modColor.initVars

If Range("G24").Value > Range("I24").Value Then
Range("H24").Value = "WON"
Range("H24").Value = "LOSE"
End If
End Sub