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!
"Bernard Liengme" wrote:
> 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
> --
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
>
> "Randy1360" <(E-Mail Removed)> wrote in message
> news:6994AE11-F26B-4A4A-9FB5-(E-Mail Removed)...
> > 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.
>
> .
>
Similar Threads
