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 !!!!

Hi

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

Best wishes Harald


"mami" skrev i melding
...
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 !!!!

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" wrote:

Hi

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

Best wishes Harald


"mami" skrev i melding
...
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 !!!!

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" skrev i melding
...
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" wrote:

Hi

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

Best wishes Harald


"mami" skrev i melding
...
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 !!!!

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" wrote:

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" skrev i melding
...
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" wrote:

Hi

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

Best wishes Harald


"mami" skrev i melding
...
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 !!!!

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" skrev i melding
...
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" wrote:

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" skrev i melding
...
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" wrote:

Hi

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

Best wishes Harald


"mami" skrev i melding
...
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 !!!!