Color rows by date

[Sj3000]

Hi,

I have a table which records different events that I am carrying out, which is often four or five a day. Column A has the dates in and then B is location C other information etc etc. I want to color the rows depending on the date in column A, preferably in alternating colors. So all events 20/01/14 one colour, then all 21/01/14 another and then all 22/01/14 the first colour.

I want to be able to use a blank template that only has one row for each date but which will automatically format as I add in extra events. This is probably really really simple but I have wasted a lot of time not working it out yet,

Thanks for the help,
Sam

[alansidman]

Look at this link.

http://office.microsoft.com/en-us/ex...010251644.aspx

[Sj3000]

The problem with that solution is it alternates row by row. I want to be able to alternate by date. For example in January I started out with a blank template but ended up with one event on 14/01/14 but then 6 on 15/01/14 and 4 on 16/01/14. I want all 6 on 15th to be the same colour etc.

I was hoping to be able to have a blank template of alternating rows where if I insert a new row below one date it would be the same colour as above?

[newdoverman]

Take a look at this example.

[Sj3000]

That's perfect thank you. Do you mind explaining the formulas if you can. It's nice to understand what I am doing, then I may manage to replicate it!

Thanks again,

Sam

[newdoverman]

In the order that the formulae are in Conditional Formatting:

1. =$A1="" means that if cell A1 is blank do not format. This must be accompanied by using Fill, No Color otherwise it will have no effect.

2. =MOD(DAY($A1),2)=1 This finds the "DAY" part of the date in A1 and the mod part divides the value of the DAY by 2. If the remainder of the division isn't 0 This determines that the day is an odd number. The formatting for odd numbered days can now be applied to the days where this condition applies.

3. =MOD(DAY($A1),2)=0 This finds the "DAY" part of the date in A1 and the mod part divides the value by 2. If the day is an even number, it, when divided by 2 will have a remainder of 0. This means that the day is an even number and the formatting for even numbered days can be applied.


The $ sign before the A is important because without it, the formatting will only be applied to column A and not the whole row.

The Applied To field in the Rules Manager, determines the range of cells that the formatting applies to. Cells outside this range are not affected by the Conditional Formatting. To edit these ranges click in the field to be edited and hit F2 otherwise a mess will result and you will have to re-establish the range that you want affected by the Conditional Formatting. In this case all the ranges are identical (or should be). Note that the cell references are Absolute.

[Sj3000]

Brilliant! Thank you so much.

[newdoverman]

You're welcome....glad to help.

Thank you for the feedback.