# Date-based conditional formatting

I have a column with dates in column A. I.e.:

``````01/01/2012
01/01/2012
01/01/2012
02/01/2012
02/01/2012
03/01/2012
04/01/2012
04/01/2012
04/01/2012
``````

Is it possible to have a conditional formatting where all rows within the same date have alternating colors (light grey/black)? Just to help the user better separate the rows within the same dates.

Thanks!

-

## migrated from stackoverflow.comOct 11 '12 at 13:01

This question came from our site for professional and enthusiast programmers.

Within the same date, alternate colours (as your text suggests) or each date alternating? Your question will lead to them being coloured as grey, black, gray/ grey, black/ grey/ grey, black, grey. How will looking for the a potential non transiton make it clearer? – Deanna Oct 11 '12 at 12:15

Assuming data starts at A2 select just that cell and apply these two rules using "use a formula to determine which cells to format"

`=A2<>""`

with light grey format

and then

`=AND(A2<>"",MOD(ROUND(SUM((A\$1:A1<>A\$2:A2)+0),9),2)=0)`

with black format

if applied in that order they will display the other way round (which is what's required)

Now change the "Applies to" range for both conditions to \$A\$2:\$A\$1000 or as far as you might need. That should format as required

-
Great! It worked so well visually that I would like to have the next 4 columns formattet the same way based on the date in the A column? How would I modify the formula to achieve this? Also - would it be possible to explain what the formula does? – ThEvensen Oct 11 '12 at 15:17
To apply to columns A to E just select A2:E2 first, rather than A2 and use the same formula but with \$ signs in front of the As, i.e.`=AND(\$A2<>"",MOD(ROUND(SUM((\$A\$1:\$A1<>\$A\$2:\$A2)+0),9),2)=0)` - after that extend "applies to" range to \$A\$2:\$E\$1000 - I'll amend my reply with an explanation later – barry houdini Oct 11 '12 at 15:41
...and the other formula needs to be edited similarly,=\$A2<>"" – barry houdini Oct 11 '12 at 16:38