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