Color Coding cell based on date (over a large range)

[Randy1360]

I have a "calendar" that I want cells color coded based on a date entered in
a reference cell. I've tried it a couple of ways using conditional
formatting (which I assume is the correct way to do it), but can't figure out
exactly how to accomplish it.

I.e., given the following type of spreadsheet entry...

A B C D E F G H I J K
1 JANUARY
2 1 2 3 4 5 6 7 8 9 ...
3 _ _ _ _ _ _ _ _ _

(C3 would be Jan 1, D3 would be Jan 2, etc)

If a reference cell has Jan 1 in it, then C3 would be highlighted a color ..
if it had Jan 2 in it, then D3 would be .. etc.

Furthermore, this would be used over many rows (i.e., a date entry for row 3
... one for row 4 .. etc)

Any help would be appreciated.

 

[Gord Dibben]

Using your sample of 9 dates in C3:K3

A1:A9 have dates Jan 1 though Jan 9

Select C3:K12 and CF>Formula is:

=C3=$A1 Format to a color and OK


Gord Dibben MS Excel MVP

 

[Bernard Liengme]

InA1 I have a date 3/1/2010 (I use the dd/mm/yyyy format) but the cell is
formatted to display 3-Jan
I selected C3:AG3 and applied this formula in the Conditional Formatting
dialog:
=AND(MONTH($A$1)=1, DAY($A$1)=C2)
and set a colour for the fill

Note the C2 (without $); this is the first cell in my selection. It has the
value 1
In I look at the conditional formatting for D3 it will read
=AND(MONTH($A$1)=1, DAY($A$1)=D2)

Since the DAY of A1 is 3, the cell E3 gets the colour fill. I I enter 28-Feb
nothing gets the fill since MONTH of A1 will then be 2.

You should be able to adapt this for the rest of your calendar.

best wishes

 

[Randy1360]

Thanks Bernard .. that got me rolling.

One change I made to yours is using a relative reference to the column
instead of an absolute for the source cell:

=AND(MONTH($A1)=1, DAY($A1)=C2)

This allows me to copy the conditional formatting across for the entire
month and only the day entered in the source cell will be highlighted.

Follow-on question: I have the month spelled out in a combined cell above
the dates (i.e., M1:AQ1 has JANUARY, AR1:BS2 has FEBRUARY, etc). Can I
change the MONTH value (i.e., 1, 2, 3, etc) based on a formula converting the
text? If so, how could you reference the month text in a combined cell
easily to do the calculation?

If the above can be done, then I can just copy the formatting to the end of
the year and not have to change the formatting for each month.

Thanks again!