Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 !!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 !!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 !!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 !!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 !!!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 !!!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 !!!! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 !!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two worksheets - macro help | Excel Discussion (Misc queries) | |||
need help with compare macro | Excel Discussion (Misc queries) | |||
compare date to various date ranges and sum value | Excel Worksheet Functions | |||
Macro to Compare Two Worksheets? | Excel Discussion (Misc queries) | |||
Macro to compare two columns of data | Excel Discussion (Misc queries) |