Macro about date compare

[mami]

I have an excel sheet that contains dates in different cells.

I want a macro which: Each time I open the excel file, compares all shells
with dates with current date and if the date in a cell has exceeded current
date, then mark the whole row somehow, e.g. color the row with yellow color.

Please please help !!!!

 

[Harald Staff]

Hi

I'd use conditional formatting for this, not a macro. Use a formula rule
like
=$D5<TODAY()

Best wishes Harald

 

[mami]

OK, I could do that.

But I don 't want this "row coloring" to happen when I edit a cell.
I want this to happen when I open the excel file.

Any ideas ???

 

[Harald Staff]

Sure

Sub Auto_open()
Dim R As Long
With Sheets(1)
For R = 2 To .UsedRange.Rows.Count
If .Cells(R, 4).Value > Date Then _
.Range(.Cells(R, 1), .Cells(R, 12)).Interior.ColorIndex = 39
Next
End With
End Sub

HTH. Best wishes Harald

 

[mami]

Sorry, but either I have done something wrong or it does not work.

E.g. at cell O67 I have a date 06/16/2008.
I run the macro (Tools -> Macro -> Run) but nothing happens.
What happens ??
Am I missing something here ???

 

[Harald Staff]

Yes, the .Cells(R, 4) checks column 4, which is the D column, not the O
column. You must either provide details like this for ready to run
solutions, or do the implementation work yourself.

Best wishes Harald

 

[mami]

OK, please see details below:

Excel sheet uses columns A till O. "Column O" contains the date-data in the
format: 16/6/2008 (stands for "16 of June 2008").
Row-number of course is not fixed. Each time I insert data, a new row is
added with the same format as above, that is every time "column O" contains
such a date.

Now I want the following to happen:

Every time I open the excel-file, a macro runs automatically and does the
following:
- checks the content of all cells under "Column O"
- if the written date in a cell is greater that current date, then highlight
the corresponding row (i.e. "Column A" till "Column O" of specific row) with
yellow color.

Hope it is more clear now !!!

Thanks in advance for all the help !!!

 

[Harald Staff]

Ok try

Sub Auto_open()
Dim R As Long
With Sheets(1)
For R = 2 To .UsedRange.Rows.Count
If .Cells(R, 15).Value > Date Then _
.Range(.Cells(R, 1), .Cells(R, 15)).Interior.ColorIndex = 6
Next
End With
End Sub


HTH. Best wishes Harald