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

  • Thread starter Thread starter Randy1360
  • Start date Start date
R

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.
 
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
 
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
 
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!
 
Back
Top