Macro about date compare
 

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

Macro about date compare
 

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

Macro about date compare
 

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

Macro about date compare
 

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

Macro about date compare
 

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

Macro about date compare
 

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

Macro about date compare
 

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

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

Macro about date compare
 

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


"mami" wrote in message
...
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" wrote:

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