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