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