+ Reply to Thread
Results 1 to 21 of 21

Calendar - conditional formatting?

Hybrid View

Guest Calendar - conditional... 06-27-2006, 09:30 AM
Guest Re: Calendar - conditional... 06-27-2006, 09:50 AM
Guest Re: Calendar - conditional... 06-27-2006, 09:55 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:00 AM
Guest Re: Calendar - conditional... 06-27-2006, 09:50 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:10 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:10 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:20 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:25 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:50 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:55 AM
Guest Re: Calendar - conditional... 06-27-2006, 11:40 AM
Guest Re: Calendar - conditional... 06-27-2006, 11:50 AM
Guest Re: Calendar - conditional... 06-27-2006, 12:10 PM
Guest Re: Calendar - conditional... 06-27-2006, 12:25 PM
Guest Re: Calendar - conditional... 06-27-2006, 11:50 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:20 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:25 AM
Guest Re: Calendar - conditional... 06-27-2006, 10:35 AM
  1. #1
    Valery2105
    Guest

    Re: 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

    >
    >


  2. #2
    Mark Driscol
    Guest

    Re: 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

    > >
    > >



  3. #3
    Valery2105
    Guest

    Re: 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
    > > >
    > > >

    >
    >


  4. #4
    Mark Driscol
    Guest

    Re: 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
    > > > >
    > > > >

    > >
    > >



  5. #5
    Valery2105
    Guest

    Re: 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
    > > > > >
    > > > > >
    > > >
    > > >

    >
    >


  6. #6
    Mark Driscol
    Guest

    Re: 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
    > > > > > >
    > > > > > >
    > > > >
    > > > >

    > >
    > >



  7. #7
    Valery2105
    Guest

    Re: 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
    > > > > > > >
    > > > > > > >
    > > > > >
    > > > > >
    > > >
    > > >

    >
    >


  8. #8
    Mark Driscol
    Guest

    Re: 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
    > > > >
    > > > >

    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1