Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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.


share|improve this question

migrated from Oct 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
up vote 1 down vote accepted

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


with light grey format

and then


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

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.