Calendar - conditional formatting?
 

Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Hi Valery,

Try something like:

'=============
Public Sub Tester()
Dim rng As Range
Dim rCell As Range

Set rng = Selection '<<==== CHANGE

For Each rCell In Selection
With rCell
.Value = DateSerial(Year(Date), 1, .Value)
End With
Next rCell

rng.NumberFormat = "d"
End Sub
'<<=============


Replace 1 with the number of the month of interest.


---
Regards,
Norman



"Valery2105" wrote in message
...
Hi, I have been searching hi and low for a solution to this and beginning
to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are
recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

You can try this.!

1 - Copy the format of the No. column and paste special ( Format ) it
to the starting date and ending date. - Now the colours have got
copied.
2 - Now just change the format of the starting date and ending date
format as date format.

Hope this is helpful.....

For any queries revert back

Regards
Thyagaraj

Calendar - conditional formatting?
 

Hi Valery,

Ignore my response - I misread your question.

However, responding to your third point:

3. How can I format the numbers in the calendar so that they are
recognised as dates?

Why not enter the calendar values as dates and format them with a custom
format of "d".


---
Regards,
Norman

Calendar - conditional formatting?
 

Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Thank you so much Mark,

I am trying to find the format 'd' but am unable to all I can find is d.m.yy
- any suggestions?


"Mark Driscol" wrote:

Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Mark, Thanks don't worry about the last post - I figured it out. Will let
you know how the rest goes.

Thanks



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Highlight the date cells you want to format, and from the menus select
Cells Format select the Number tab, select the Custom category, and
in the Type box erase whatever is there and enter "d" (without the
quotes).

Mark


Valery2105 wrote:
Thank you so much Mark,

I am trying to find the format 'd' but am unable to all I can find is d.m.yy
- any suggestions?


"Mark Driscol" wrote:

Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

If the entire range of cells A2:E30 (in this example) is named
"DataTable", the code will work. Do you know how to name this range so
that all 29 rows of five cells are included?

Mark


Valery2105 wrote:
Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

No, I am not too sure how to name the range - what I have done is named range
X5:AC33 as DataTable but only the first row X5:Ac5 works?


"Mark Driscol" wrote:

If the entire range of cells A2:E30 (in this example) is named
"DataTable", the code will work. Do you know how to name this range so
that all 29 rows of five cells are included?

Mark


Valery2105 wrote:
Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Can you describe the actual layout of your spreadsheet? What cells
contain the Calendar and DataTable entries? From the menus, if you go
to Insert Name Define, and then click on the name "DataTable", what
does it say the address is?

Since I used a For Each statement, it should loop through each row of
your DataTable range and and do the same thing for each row.

Mark


Valery2105 wrote:
No, I am not too sure how to name the range - what I have done is named range
X5:AC33 as DataTable but only the first row X5:Ac5 works?


"Mark Driscol" wrote:

If the entire range of cells A2:E30 (in this example) is named
"DataTable", the code will work. Do you know how to name this range so
that all 29 rows of five cells are included?

Mark


Valery2105 wrote:
Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

By the way, X5:AC5 contains six cells, not five. In the code I gave
you, I referenced specific cells (Cells(1), Cells(5), etc.). If you
have more than five cells in each row, the code would have to be
modified.

Mark


Valery2105 wrote:
No, I am not too sure how to name the range - what I have done is named range
X5:AC33 as DataTable but only the first row X5:Ac5 works?


"Mark Driscol" wrote:

If the entire range of cells A2:E30 (in this example) is named
"DataTable", the code will work. Do you know how to name this range so
that all 29 rows of five cells are included?

Mark


Valery2105 wrote:
Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Address field for Calendar is as follows:

='Training Calendar (2)'! $B$5:$V$36

for the DataTable:

='Training Calendar (2)'! $X$5:$AC$33



"Mark Driscol" wrote:

Can you describe the actual layout of your spreadsheet? What cells
contain the Calendar and DataTable entries? From the menus, if you go
to Insert Name Define, and then click on the name "DataTable", what
does it say the address is?

Since I used a For Each statement, it should loop through each row of
your DataTable range and and do the same thing for each row.

Mark


Valery2105 wrote:
No, I am not too sure how to name the range - what I have done is named range
X5:AC33 as DataTable but only the first row X5:Ac5 works?


"Mark Driscol" wrote:

If the entire range of cells A2:E30 (in this example) is named
"DataTable", the code will work. Do you know how to name this range so
that all 29 rows of five cells are included?

Mark


Valery2105 wrote:
Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Your DataTable has six columns, not the five you mentioned. What are
the six column headers?

Mark


Valery2105 wrote:
Address field for Calendar is as follows:

='Training Calendar (2)'! $B$5:$V$36

for the DataTable:

='Training Calendar (2)'! $X$5:$AC$33



"Mark Driscol" wrote:

Can you describe the actual layout of your spreadsheet? What cells
contain the Calendar and DataTable entries? From the menus, if you go
to Insert Name Define, and then click on the name "DataTable", what
does it say the address is?

Since I used a For Each statement, it should loop through each row of
your DataTable range and and do the same thing for each row.

Mark


Valery2105 wrote:
No, I am not too sure how to name the range - what I have done is named range
X5:AC33 as DataTable but only the first row X5:Ac5 works?


"Mark Driscol" wrote:

If the entire range of cells A2:E30 (in this example) is named
"DataTable", the code will work. Do you know how to name this range so
that all 29 rows of five cells are included?

Mark


Valery2105 wrote:
Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

Apologiese for that -

There are only 5 column headers (1 (No.) of which is merged with Training
Title - but only the header) I forgot about the Int/Ext header.

eg

Training Title Training Description Int / Ext START DATE END DATE
1 Excel Pivot Table 01/01/2006
06/01/2006
2 Word Merging Cells

Training title Training Description Int/Ext Start Date End
Date

No. Excel

"Mark Driscol" wrote:

Your DataTable has six columns, not the five you mentioned. What are
the six column headers?

Mark


Valery2105 wrote:
Address field for Calendar is as follows:

='Training Calendar (2)'! $B$5:$V$36

for the DataTable:

='Training Calendar (2)'! $X$5:$AC$33



"Mark Driscol" wrote:

Can you describe the actual layout of your spreadsheet? What cells
contain the Calendar and DataTable entries? From the menus, if you go
to Insert Name Define, and then click on the name "DataTable", what
does it say the address is?

Since I used a For Each statement, it should loop through each row of
your DataTable range and and do the same thing for each row.

Mark


Valery2105 wrote:
No, I am not too sure how to name the range - what I have done is named range
X5:AC33 as DataTable but only the first row X5:Ac5 works?


"Mark Driscol" wrote:

If the entire range of cells A2:E30 (in this example) is named
"DataTable", the code will work. Do you know how to name this range so
that all 29 rows of five cells are included?

Mark


Valery2105 wrote:
Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks

Calendar - conditional formatting?
 

But your range X5:AC5 contains six cells. Should this go from X5:AB5?
You have defined your named range as containing six columns, not five.

Mark


Valery2105 wrote:
Apologiese for that -

There are only 5 column headers (1 (No.) of which is merged with Training
Title - but only the header) I forgot about the Int/Ext header.

eg

Training Title Training Description Int / Ext START DATE END DATE
1 Excel Pivot Table 01/01/2006
06/01/2006
2 Word Merging Cells

Training title Training Description Int/Ext Start Date End
Date

No. Excel

"Mark Driscol" wrote:

Your DataTable has six columns, not the five you mentioned. What are
the six column headers?

Mark


Valery2105 wrote:
Address field for Calendar is as follows:

='Training Calendar (2)'! $B$5:$V$36

for the DataTable:

='Training Calendar (2)'! $X$5:$AC$33



"Mark Driscol" wrote:

Can you describe the actual layout of your spreadsheet? What cells
contain the Calendar and DataTable entries? From the menus, if you go
to Insert Name Define, and then click on the name "DataTable", what
does it say the address is?

Since I used a For Each statement, it should loop through each row of
your DataTable range and and do the same thing for each row.

Mark


Valery2105 wrote:
No, I am not too sure how to name the range - what I have done is named range
X5:AC33 as DataTable but only the first row X5:Ac5 works?


"Mark Driscol" wrote:

If the entire range of cells A2:E30 (in this example) is named
"DataTable", the code will work. Do you know how to name this range so
that all 29 rows of five cells are included?

Mark


Valery2105 wrote:
Mark,

This has worked but only for the 1st row and for January calendar. Do I
have to repeat the code you provided a few times to cover all 29 rows?



"Mark Driscol" wrote:

One slight change that will delete a color if a date no longer appears
in your table.

If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then
' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
Else
' Added this line
rngCell.Interior.ColorIndex = xlNone
End If

Also, you mentioned wanting this to happen automatically when you enter
a date. Depending on what else you have going on in the spreadsheet,
you may not want this to happen, but you could use the worksheet's
Change event to automatically trigger a macro like this to run whenever
you change a cell value.

Mark



Mark Driscol wrote:
Say your 29 rows of data are in cells A2:E30 and that this range is
named "DataTable" (column headers are directly above this). Also, say
your calendar (I will just assume it has January for this example)
dates are in cells G2:M6 and that this range is named "Calendar" (the
word January is centered in the cells above this).

The following macro worked for me to color your cells.


Option Explicit

Sub ColorCalendar()

Dim row As Range
Dim rngCell As Range

For Each rngCell In Range("Calendar").Cells
If Len(rngCell.Value) < 0 Then

' Cell is not blank
For Each row In Range("DataTable").Rows

' See if this date falls between Start and End Dates
If rngCell.Value = row.Cells(4).Value And _
rngCell.Value <= row.Cells(5).Value Then

' Use same color for this date on calendar
rngCell.Interior.ColorIndex = _
row.Cells(1).Interior.ColorIndex
End If
Next row
End If
Next rngCell

End Sub


Put actual dates on your calendar and format them as "d".


Mark


Valery2105 wrote:
Hi, I have been searching hi and low for a solution to this and beginning to
think it may not be possible, please help you are my last hope.

I have a worksheet that has a 12 month calendar on it formatted as below:

January

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

1. I have 5 columns in the same worksheet:

No. Training Title Description Start Date End Date
1 Excel Pivot Table 06/07/06 08/07/06

The No. & Training Title columns are colour filled (there are 29 rows with
different colours)

2. When inputting the start and end date I would like the calendar to
automatically fill with the same colour as in the No. and Training Title.

3. How can I format the numbers in the calendar so that they are recognised
as dates?

Thanks