As Martijn has said Conditional Formatting is the perfect tool for your
requirements and that applies to you updated request as well. However as
you have stated you would like to do this with VBA as a learning
exercise one way would be like this. Assuming dates in column F starting
in Row2:

Sub Macro1()

Dim cell As Range
Dim eRow As Long

eRow = Cells(Rows.Count, "F").End(xlUp).Row

For Each cell In Range("F2:F" & eRow)
If IsDate(cell.Value) Then
Select Case DateDiff("M", Date, cell.Value)
Case 0
cell.Interior.ColorIndex = 3
Case 1
cell.Interior.ColorIndex = 6
Case 2
cell.Interior.ColorIndex = 5
Case Else
cell.Interior.ColorIndex = xlNone
End Select
End If
Next cell

End Sub

Hope this helps
Rowan

Luc wrote:
Martijn,

Thank you for your propmpt answer.
I didn't know that feature and tried it. It effectively is very good and
simple!

I have further thought about my need and would like to submit a more precise
request.
I still have my equipment list with their limit date (all the dates in the
same column).

In the worksheet, I would like to have a button "Check status" that makes
the following actions when clicked:
- all the dates of the current month (e.g.: November 2005) to get RED;
- all the dates of the current month + 1 (e.g.: December 2005) to get YELLOW;
- all the dates of the current month + 2 (e.g.: January 2006) to get BLUE.
Excel should automatically get the current month from windows clock.

I thank you in advance if you can help for that. That example will allow me
to start working with simple codes and adapt existing example to my needs.

Greatings
Luc

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

"Martijn" wrote:


Luc,

Use the standard feature "Conditional format" in your menu. You don't have
to program something.

grtx's M.

"Luc" schreef in bericht
...

Hello Ron,

I have a similar need. Hope you can help...
I have a worksheet with a list of equipment and their relative date of
validity (time limit for use).
I would like a code that highlight in red colour for example all the dates
that are within 45 days.

Thanks for your support.
Luc

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

"Ron Rosenfeld" wrote:


On Tue, 9 Aug 2005 08:02:05 -0700, Kjeldc



wrote:


Memberfee is due every 1. day of month. How do I make a automatic check

for

the date when the workbook open?

Right click on some worksheet tab and select View Code.

In the project explorer window, select the line in your project that

says "This

Workbook". Double click on that line and paste the code below into the

window

that opens:

====================================
Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox ("Member Fee Due")
End If
End Sub
================================

Obviously, you may want to pretty this up a bit. But event-driven

macros are

the key, here.


--ron