0

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!

Improve this question
1
  • 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, 2012 at 12:15

1 Answer 1

Reset to default
1

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

Improve this answer
3
  • 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, 2012 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, 2012 at 15:41
  • ...and the other formula needs to be edited similarly,=$A2<>""
    – barry houdini
    Oct 11, 2012 at 16:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .