Date formatiing - using code to block months together
 

day I have a row of dates which start from =TODAY() and increment by 1 along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month row to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in message
...
day I have a row of dates which start from =TODAY() and increment by 1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works, but the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in message
...
day I have a row of dates which start from =TODAY() and increment by 1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

I'm not entirely sure I understand what you are looking for. Let's look at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and copy
the across to D1 (of course, you would do this as far across as necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008, 8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August, September and
September in them. What is it that you want to show under these (that is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works, but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment by 1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show
the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Hi Rick,

For the month row I want to display just a single August or September label
under that row of day dates. But I need it to update as the dates change

So, for September, the month row will be a single horizontally formatted
merged cell with just "September" below the vertically formatted row of days
for that month. This would be the same for subsequent months of course. I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's look at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and copy
the across to D1 (of course, you would do this as far across as necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008, 8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August, September and
September in them. What is it that you want to show under these (that is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works, but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment by 1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show
the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Okay, I needed to make a couple of assumptions. First, I assumed that you
have a real date in the first column of your date row and all the other
dates in that row are generated by a formula that adds one to the previous
column. This way, changing a single cell (the first column of the date row)
will change all the other dates automatically; so I keyed the solution onto
your changing that cell only. Second, I assumed you will *never* have a date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in order to
bring up the proper window). Immediately after you paste the code into the
code window, change the two Const statements at the very beginning of the
code to reflect your actual worksheet layout. The DateRow constant is the
row where the dates are located and the StartColumn is the column number of
the first date in that row. After you have done that, go to the worksheet
and enter a date into that first date cell.... you should see the merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value + 32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value = MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X - CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

For the month row I want to display just a single August or September
label
under that row of day dates. But I need it to update as the dates change

So, for September, the month row will be a single horizontally formatted
merged cell with just "September" below the vertically formatted row of
days
for that month. This would be the same for subsequent months of course. I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August, September
and
September in them. What is it that you want to show under these (that is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show
the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Hi Rick,

Thanks for looking into this

There appears to be a word wrap problem with the code.

Can you take a look?

Thanks,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed that you
have a real date in the first column of your date row and all the other
dates in that row are generated by a formula that adds one to the previous
column. This way, changing a single cell (the first column of the date row)
will change all the other dates automatically; so I keyed the solution onto
your changing that cell only. Second, I assumed you will *never* have a date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in order to
bring up the proper window). Immediately after you paste the code into the
code window, change the two Const statements at the very beginning of the
code to reflect your actual worksheet layout. The DateRow constant is the
row where the dates are located and the StartColumn is the column number of
the first date in that row. After you have done that, go to the worksheet
and enter a date into that first date cell.... you should see the merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value + 32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value = MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X - CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

For the month row I want to display just a single August or September
label
under that row of day dates. But I need it to update as the dates change

So, for September, the month row will be a single horizontally formatted
merged cell with just "September" below the vertically formatted row of
days
for that month. This would be the same for subsequent months of course. I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August, September
and
September in them. What is it that you want to show under these (that is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show
the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when the
spreadsheet opens?

All the best,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed that you
have a real date in the first column of your date row and all the other
dates in that row are generated by a formula that adds one to the previous
column. This way, changing a single cell (the first column of the date row)
will change all the other dates automatically; so I keyed the solution onto
your changing that cell only. Second, I assumed you will *never* have a date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in order to
bring up the proper window). Immediately after you paste the code into the
code window, change the two Const statements at the very beginning of the
code to reflect your actual worksheet layout. The DateRow constant is the
row where the dates are located and the StartColumn is the column number of
the first date in that row. After you have done that, go to the worksheet
and enter a date into that first date cell.... you should see the merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value + 32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value = MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X - CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

For the month row I want to display just a single August or September
label
under that row of day dates. But I need it to update as the dates change

So, for September, the month row will be a single horizontally formatted
merged cell with just "September" below the vertically formatted row of
days
for that month. This would be the same for subsequent months of course. I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August, September
and
September in them. What is it that you want to show under these (that is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show
the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Before I can answer that question, you will need to tell me how your dates
are handled. That first date... how it it entered into the cell? Do you
enter the date manually? Do you have a formula in it? If you are using a
formula, what it that formula?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when the
spreadsheet opens?

All the best,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed that you
have a real date in the first column of your date row and all the other
dates in that row are generated by a formula that adds one to the
previous
column. This way, changing a single cell (the first column of the date
row)
will change all the other dates automatically; so I keyed the solution
onto
your changing that cell only. Second, I assumed you will *never* have a
date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in order
to
bring up the proper window). Immediately after you paste the code into
the
code window, change the two Const statements at the very beginning of the
code to reflect your actual worksheet layout. The DateRow constant is the
row where the dates are located and the StartColumn is the column number
of
the first date in that row. After you have done that, go to the worksheet
and enter a date into that first date cell.... you should see the merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value + 32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value = MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X -
CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround
xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Rick,

For the month row I want to display just a single August or September
label
under that row of day dates. But I need it to update as the dates
change

So, for September, the month row will be a single horizontally
formatted
merged cell with just "September" below the vertically formatted row of
days
for that month. This would be the same for subsequent months of course.
I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's
look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is
that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August,
September
and
September in them. What is it that you want to show under these (that
is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment
by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to
show
the
blocks of months for the day date row above- January, February
etc

The day date row of course updates each day so i would like the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Hi Rick,

the start date has a formula in it =TODAY()

Roger

"Rick Rothstein" wrote:

Before I can answer that question, you will need to tell me how your dates
are handled. That first date... how it it entered into the cell? Do you
enter the date manually? Do you have a formula in it? If you are using a
formula, what it that formula?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when the
spreadsheet opens?

All the best,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed that you
have a real date in the first column of your date row and all the other
dates in that row are generated by a formula that adds one to the
previous
column. This way, changing a single cell (the first column of the date
row)
will change all the other dates automatically; so I keyed the solution
onto
your changing that cell only. Second, I assumed you will *never* have a
date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in order
to
bring up the proper window). Immediately after you paste the code into
the
code window, change the two Const statements at the very beginning of the
code to reflect your actual worksheet layout. The DateRow constant is the
row where the dates are located and the StartColumn is the column number
of
the first date in that row. After you have done that, go to the worksheet
and enter a date into that first date cell.... you should see the merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value + 32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value = MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X -
CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround
xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Rick,

For the month row I want to display just a single August or September
label
under that row of day dates. But I need it to update as the dates
change

So, for September, the month row will be a single horizontally
formatted
merged cell with just "September" below the vertically formatted row of
days
for that month. This would be the same for subsequent months of course.
I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's
look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is
that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August,
September
and
September in them. What is it that you want to show under these (that
is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment
by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to
show
the
blocks of months for the day date row above- January, February
etc

The day date row of course updates each day so i would like the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Open up the workbook code window (double click the ThisWorkbook item in the
project window) and copy/paste this code into it...

Private Sub Workbook_Open()
Sheet1.AddMergedMonthNames
End Sub

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

the start date has a formula in it =TODAY()

Roger

"Rick Rothstein" wrote:

Before I can answer that question, you will need to tell me how your
dates
are handled. That first date... how it it entered into the cell? Do you
enter the date manually? Do you have a formula in it? If you are using a
formula, what it that formula?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when the
spreadsheet opens?

All the best,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed that
you
have a real date in the first column of your date row and all the
other
dates in that row are generated by a formula that adds one to the
previous
column. This way, changing a single cell (the first column of the date
row)
will change all the other dates automatically; so I keyed the solution
onto
your changing that cell only. Second, I assumed you will *never* have
a
date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste
all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in
order
to
bring up the proper window). Immediately after you paste the code into
the
code window, change the two Const statements at the very beginning of
the
code to reflect your actual worksheet layout. The DateRow constant is
the
row where the dates are located and the StartColumn is the column
number
of
the first date in that row. After you have done that, go to the
worksheet
and enter a date into that first date cell.... you should see the
merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value + 32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value =
MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X -
CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround
xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Rick,

For the month row I want to display just a single August or
September
label
under that row of day dates. But I need it to update as the dates
change

So, for September, the month row will be a single horizontally
formatted
merged cell with just "September" below the vertically formatted row
of
days
for that month. This would be the same for subsequent months of
course.
I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's
look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1
and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in
these
columns on Row 2 you want the month names for the dates in Row 1,
is
that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August,
September
and
September in them. What is it that you want to show under these
(that
is
what I am having trouble understanding from your "block"
reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give
works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April
for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote
in
message
...
day I have a row of dates which start from =TODAY() and
increment
by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates
to
show
the
blocks of months for the day date row above- January, February
etc

The day date row of course updates each day so i would like
the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Rick - thanks for the code, but it doesnt seem to like the "Sheet1" bit

The code is associated with the sheet "Project Plan". I tried substituting
it but it didnt work.

What do you think?

Rog

"Rick Rothstein" wrote:

Open up the workbook code window (double click the ThisWorkbook item in the
project window) and copy/paste this code into it...

Private Sub Workbook_Open()
Sheet1.AddMergedMonthNames
End Sub

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

the start date has a formula in it =TODAY()

Roger

"Rick Rothstein" wrote:

Before I can answer that question, you will need to tell me how your
dates
are handled. That first date... how it it entered into the cell? Do you
enter the date manually? Do you have a formula in it? If you are using a
formula, what it that formula?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when the
spreadsheet opens?

All the best,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed that
you
have a real date in the first column of your date row and all the
other
dates in that row are generated by a formula that adds one to the
previous
column. This way, changing a single cell (the first column of the date
row)
will change all the other dates automatically; so I keyed the solution
onto
your changing that cell only. Second, I assumed you will *never* have
a
date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste
all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in
order
to
bring up the proper window). Immediately after you paste the code into
the
code window, change the two Const statements at the very beginning of
the
code to reflect your actual worksheet layout. The DateRow constant is
the
row where the dates are located and the StartColumn is the column
number
of
the first date in that row. After you have done that, go to the
worksheet
and enter a date into that first date cell.... you should see the
merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value + 32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value =
MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X -
CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround
xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Rick,

For the month row I want to display just a single August or
September
label
under that row of day dates. But I need it to update as the dates
change

So, for September, the month row will be a single horizontally
formatted
merged cell with just "September" below the vertically formatted row
of
days
for that month. This would be the same for subsequent months of
course.
I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's
look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1
and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in
these
columns on Row 2 you want the month names for the dates in Row 1,
is
that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August,
September
and
September in them. What is it that you want to show under these
(that
is
what I am having trouble understanding from your "block"
reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give
works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April
for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote
in
message
...
day I have a row of dates which start from =TODAY() and
increment
by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates
to
show
the
blocks of months for the day date row above- January, February
etc

The day date row of course updates each day so i would like
the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Whoops! Sorry, that should have been...

Worksheets("Sheet1").AddMergedMonthNames

where you would substitute the name of your worksheet (in quotes) in place
of the example "Sheet1" that I used.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Open up the workbook code window (double click the ThisWorkbook item in
the project window) and copy/paste this code into it...

Private Sub Workbook_Open()
Sheet1.AddMergedMonthNames
End Sub

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

the start date has a formula in it =TODAY()

Roger

"Rick Rothstein" wrote:

Before I can answer that question, you will need to tell me how your
dates
are handled. That first date... how it it entered into the cell? Do you
enter the date manually? Do you have a formula in it? If you are using a
formula, what it that formula?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when the
spreadsheet opens?

All the best,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed that
you
have a real date in the first column of your date row and all the
other
dates in that row are generated by a formula that adds one to the
previous
column. This way, changing a single cell (the first column of the
date
row)
will change all the other dates automatically; so I keyed the
solution
onto
your changing that cell only. Second, I assumed you will *never* have
a
date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste
all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in
order
to
bring up the proper window). Immediately after you paste the code
into
the
code window, change the two Const statements at the very beginning of
the
code to reflect your actual worksheet layout. The DateRow constant is
the
row where the dates are located and the StartColumn is the column
number
of
the first date in that row. After you have done that, go to the
worksheet
and enter a date into that first date cell.... you should see the
merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value +
32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value =
MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X -
CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround
xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Rick,

For the month row I want to display just a single August or
September
label
under that row of day dates. But I need it to update as the dates
change

So, for September, the month row will be a single horizontally
formatted
merged cell with just "September" below the vertically formatted
row of
days
for that month. This would be the same for subsequent months of
course.
I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's
look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1
and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in
these
columns on Row 2 you want the month names for the dates in Row 1,
is
that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August,
September
and
September in them. What is it that you want to show under these
(that
is
what I am having trouble understanding from your "block"
reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give
works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April
for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote
in
message
...
day I have a row of dates which start from =TODAY() and
increment
by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates
to
show
the
blocks of months for the day date row above- January,
February
etc

The day date row of course updates each day so i would like
the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

Hi Rick,

Thanks so much for all your help. I am learning so much.

I wish you all the best - have a great week,

Roger



"Rick Rothstein" wrote:

Whoops! Sorry, that should have been...

Worksheets("Sheet1").AddMergedMonthNames

where you would substitute the name of your worksheet (in quotes) in place
of the example "Sheet1" that I used.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Open up the workbook code window (double click the ThisWorkbook item in
the project window) and copy/paste this code into it...

Private Sub Workbook_Open()
Sheet1.AddMergedMonthNames
End Sub

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

the start date has a formula in it =TODAY()

Roger

"Rick Rothstein" wrote:

Before I can answer that question, you will need to tell me how your
dates
are handled. That first date... how it it entered into the cell? Do you
enter the date manually? Do you have a formula in it? If you are using a
formula, what it that formula?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when the
spreadsheet opens?

All the best,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed that
you
have a real date in the first column of your date row and all the
other
dates in that row are generated by a formula that adds one to the
previous
column. This way, changing a single cell (the first column of the
date
row)
will change all the other dates automatically; so I keyed the
solution
onto
your changing that cell only. Second, I assumed you will *never* have
a
date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste
all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in
order
to
bring up the proper window). Immediately after you paste the code
into
the
code window, change the two Const statements at the very beginning of
the
code to reflect your actual worksheet layout. The DateRow constant is
the
row where the dates are located and the StartColumn is the column
number
of
the first date in that row. After you have done that, go to the
worksheet
and enter a date into that first date cell.... you should see the
merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value +
32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value =
MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X -
CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround
xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Rick,

For the month row I want to display just a single August or
September
label
under that row of day dates. But I need it to update as the dates
change

So, for September, the month row will be a single horizontally
formatted
merged cell with just "September" below the vertically formatted
row of
days
for that month. This would be the same for subsequent months of
course.
I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's
look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1
and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in
these
columns on Row 2 you want the month names for the dates in Row 1,
is
that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August,
September
and
September in them. What is it that you want to show under these
(that
is
what I am having trouble understanding from your "block"
reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give
works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April
for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote
in
message
...
day I have a row of dates which start from =TODAY() and
increment
by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates
to
show
the
blocks of months for the day date row above- January,
February
etc

The day date row of course updates each day so i would like
the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Date formatiing - using code to block months together
 

I am glad this all worked out for you.

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

Thanks so much for all your help. I am learning so much.

I wish you all the best - have a great week,

Roger



"Rick Rothstein" wrote:

Whoops! Sorry, that should have been...

Worksheets("Sheet1").AddMergedMonthNames

where you would substitute the name of your worksheet (in quotes) in
place
of the example "Sheet1" that I used.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Open up the workbook code window (double click the ThisWorkbook item in
the project window) and copy/paste this code into it...

Private Sub Workbook_Open()
Sheet1.AddMergedMonthNames
End Sub

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Rick,

the start date has a formula in it =TODAY()

Roger

"Rick Rothstein" wrote:

Before I can answer that question, you will need to tell me how your
dates
are handled. That first date... how it it entered into the cell? Do
you
enter the date manually? Do you have a formula in it? If you are
using a
formula, what it that formula?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when
the
spreadsheet opens?

All the best,

Roger



"Rick Rothstein" wrote:

Okay, I needed to make a couple of assumptions. First, I assumed
that
you
have a real date in the first column of your date row and all the
other
dates in that row are generated by a formula that adds one to the
previous
column. This way, changing a single cell (the first column of the
date
row)
will change all the other dates automatically; so I keyed the
solution
onto
your changing that cell only. Second, I assumed you will *never*
have
a
date
(in the date row) in the *last* column in the spreadsheet.
Copy/Paste
all
the code below into the worksheet's code window where you want
this
functionality (right click the worksheet's tab, select View Code
in
order
to
bring up the proper window). Immediately after you paste the code
into
the
code window, change the two Const statements at the very beginning
of
the
code to reflect your actual worksheet layout. The DateRow constant
is
the
row where the dates are located and the StartColumn is the column
number
of
the first date in that row. After you have done that, go to the
worksheet
and enter a date into that first date cell.... you should see the
merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value
+
32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value =
MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X -
CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround
xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Rick,

For the month row I want to display just a single August or
September
label
under that row of day dates. But I need it to update as the
dates
change

So, for September, the month row will be a single horizontally
formatted
merged cell with just "September" below the vertically formatted
row of
days
for that month. This would be the same for subsequent months of
course.
I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for.
Let's
look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in
B1
and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in
these
columns on Row 2 you want the month names for the dates in Row
1,
is
that
correct? So, since today is August 30, A1 thru D1 have
8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August,
September
and
September in them. What is it that you want to show under these
(that
is
what I am having trouble understanding from your "block"
reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote
in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give
works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for
April
for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel"
wrote
in
message
...
day I have a row of dates which start from =TODAY() and
increment
by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the
dates
to
show
the
blocks of months for the day date row above- January,
February
etc

The day date row of course updates each day so i would
like
the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger