Formatting a Calendar

[rarmont]

I need to format a calendar that changes the cell color every two days (for a
total of eight colors) for the entire year of 2009, but I can't find a way to
do it. In other words, the first two days the cells are brown, the second
two days the cells are light blue, the third two days the cells are pink,
fourth = yellow, fifth = dark blue, sixth = light green, seventh = dark
green, and eighth = orange.

Is there a way to do this and repeat it until the end of the year?

For logic purposes, this is for a days' off schedule. There are eight
groups of employees and the groups have two days off for each time their
color lands on the calendar.

 

[Bernie Deitrick]

Select your cells and run this macro:

Sub TryNow()
Dim myC As Range
Dim i As Integer
Dim myColor As Integer
Dim myArray As Variant
myArray = Array(34, 35, 6, 40, 37, 39, 38, 15)
For Each myC In Selection
i = Int((myC.Value Mod 16) / 2)
myColor = myArray(i)
myC.Interior.ColorIndex = myColor
Next myC
End Sub

Change the 34, 35, 6, 40, 37, 39, 38, 15 to the colorindex values that you want to use.

And, if the days are not grouped properly, change

i = Int((myC.Value Mod 16) / 2)
to
i = Int(((myC.Value+1) Mod 16) / 2)

HTH,
Bernie
MS Excel MVP