you can do this with VB. i just tried this in a sample
workbook and it seemed to work.
first, i named the range that contained the expiration
dates "exp.dates." if you haven't named a range before,
the help explains it pretty well, but basically you
select the range that contains the dates, then go to
Insert/Name/Define. a dialog box will come up with the
range you highlighted in the Refers To box. in the Names
in workbook field, type "exp.dates" (without the
quotation marks), then hit OK.
then you can hit alt + f11 to open the Visual Basic
editor. look for the name of your workbook in the
VBAProject window. double click on the ThisWorkbook icon
and blank sheet will open up. you can then paste the
following code onto that sheet:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng As range, cl As range
Set rng = ThisWorkbook.Names("exp.dates").RefersToRange
For Each cl In rng
If cl.Interior.ColorIndex = 19 Then
cl.Interior.ColorIndex = 0
Next cl
End Sub
Private Sub Workbook_Open()
Dim rng As range, cl As range, counter As Integer
counter = 0
Set rng = ThisWorkbook.Names("exp.dates").RefersToRange
For Each cl In rng
If cl.Value = Date Then
cl.Interior.ColorIndex = 19
'you may need to change the 19 to a more
favorable color
counter = counter + 1
Else
End If
Next cl
If counter > 0 Then
MsgBox "There are " & counter & " dates that expire
today that have been highlighted."
Else
End If
End Sub
this will evaluate the dates when you open the workbook
and highlight the cells that match the system date on
your computer. it will also give you a pop up box that
will tell you how many there are, unless there are zero.
then, when you close the workbook, it will remove the
highlighting.
hope this helps. if you have any other questions on this,
you might try the excel.programming workgroup. the guys
there are much better at VB....
mike
