Conditional Formating - Annual Planner help needed

[TheTassieBFG]

Hi All

Sorry if this is a repost - I had written a post earlier today but I can not see it at all so am trying again.

I am attempting to create an annual planner (akin to a good old fashioned one that covers half a wall) and am running into a couple of issues early on in the piece. I have the months in column format and the idea is to have excel lookup dates exported from outlook/google and to place swatches of colour & Text for relevant events/holidays.

Assume the table below shows the first few days of january, B1 is the date shown as "dd" C2 date as "ddd" this continues down the column till the 31st. days dates can be changed by changing the year in another portion of the spreadsheet and are therefore fluid/subject to change

	a	b	c	d	e	f	g	h
1	01	sun
2
3	02	Mon
4
5	03	Tue

<tbody>
</tbody>

What I am trying to do is highlight Sundays ie the range of A1:H2 I have found a formula that will do the trick =WEEKDAY(A1,2)>6 but when i use it with conditional formating it is not working as anticipated. If I use absolute referencing $A$1 it works but then copying to all other days has Excel looking at A1 which isn't useful
A$1 highlights A1:A2
A1 highlights A1
can anyone tell me what I am doing wrong. I am sure there must be an easier way of doing this than formatting 364 times (or doing it for every day)

I would be happy to change the layout so that the days are static and the dates move but did not know how to do this and couldn't find a formula to do it,

If you think I am reinventing the wheel point me (link me) in the direction of the wheel as I haven't been able to find anything but simple templates with no editing capabilities and one really nice set up (Yearplanner Maker v4.1 put out by mygadgetlife) unfortunately it only allows one colour per day (each day is a single cell I am attempting several cells per day in the hopes that i can also have multiple events showing different colour blocks)

thanks for looking

Andrew

 

[Redwolfx]

Try $A1

 

[TheTassieBFG]

Try $A1

Thanks for the reponse
I left that out of the message sorry $a1 higlights A1:H1 and I need it to go A1:H2

Was slightly asleep when I placed this message

I appreciate your help though
Andrew

 

[TheTassieBFG]

I,ve found a way to have the dates move around the days so help is no longer needed. Thanks for looking (Still interested to know why it wasn't working though)