Coloring rows based on identical dates

[HowToDoIt]

I have a spreadsheet with several entries/rows on the same date, 10/10/24, for example. I'd like all rows on that date to be colored blue. Then I want the next rows on a subsequent date, to be yellow. The next three or more rows of the date following that should revert to blue, and all the rows with the date after that to go back to yellow and follow that alternating pattern of blue to yellow for the full length of the sheet. You'd have, say, three blue rows followed by three or more yellow rows followed by several blue rows followed by several yellow rows. Is that possible through, maybe, the conditional formatting feature or by yet some other approach? A simple "no" will do, but if the answer is yes then please tell me how to do it. I'd be most appreciative. I don't use Excel very often, so it's just not an issue I've ever previously dealt with.

 

[Joe4]

As long as your records are all sorted by date, you can use Conditional Formatting to do this, and applying the ISODD and ISEVEN formulas to the unique count of your date column.

Let's say that your date column is column A, and the first row of data starts on row 2.
Then select your whole data range, starting from cell A2, and apply these two Conditional Formatting Rules/formulas.

Rule 1:

=ISODD(SUM(IF(FREQUENCY($A$2:$A2,$A$2:$A2)>0,1)))

choose yellow highlighting color

Rule 2:

=ISEVEN(SUM(IF(FREQUENCY($A$2:$A2,$A$2:$A2)>0,1)))

choose gray highlighting color

So here is what my final result looks like:

Note that unique count formulas were taken from this link here: Count unique values among duplicates - Microsoft Support

 

[HowToDoIt]

As long as your records are all sorted by date, you can use Conditional Formatting to do this, and applying the ISODD and ISEVEN formulas to the unique count of your date column.

Let's say that your date column is column A, and the first row of data starts on row 2.
Then select your whole data range, starting from cell A2, and apply these two Conditional Formatting Rules/formulas.

Rule 1:

=ISODD(SUM(IF(FREQUENCY($A$2:$A2,$A$2:$A2)>0,1)))

choose yellow highlighting color

Rule 2:

=ISEVEN(SUM(IF(FREQUENCY($A$2:$A2,$A$2:$A2)>0,1)))

choose gray highlighting color

So here is what my final result looks like:
View attachment 104358

Note that unique count formulas were taken from this link here: Count unique values among duplicates - Microsoft Support

Thank you for this! I can't imagine how you would learn such a thing or even what it is that you did learn in order to apply such a forumla (for lack of a better word). I have copied both lines of code into my word processor, but when it comes to Excel I don't have a clue where and how to copy that text in order to achieve the desired formatting. I click on the conditional formatting icon but even when I click on "new rules" it doesn't present me with an opportunity to copy that text into a box. Here's what I see, and clicking on options other than "2-Color Scale" doesn't seen be the solution either. I'm sure my level of ignorance is exasperating for you, but if you can bear with me for just one or two more steps . . . .?

 

[Joe4]

Follow these steps:

1. Select the full range you want to apply this color-coding to
2. Go to Conditional Formatting
3. Select "New Rule"
4. Select the "Use a formula to determine which cells to format" Rule Type (should be last one in the list)
5. Enter the Conditional Formatting formula in the formula box
6. Click on the Format button
7. Go to the "Fill" tab and select your desired color
6. Click the "OK" button twice

Then repeat those steps again to add the other rule.

 

[HowToDoIt]

Follow these steps:

1. Select the full range you want to apply this color-coding to
2. Go to Conditional Formatting
3. Select "New Rule"
4. Select the "Use a formula to determine which cells to format" Rule Type (should be last one in the list)
5. Enter the Conditional Formatting formula in the formula box
6. Click on the Format button
7. Go to the "Fill" tab and select your desired color
6. Click the "OK" button twice

Then repeat those steps again to add the other rule.

Thank you!!! You're brilliant. In defense of my own apparent stupidity: I was trying to follow your instructions in my Mac's version of Excel. The option "Use a formula to determine which cells to format" did not appear in that version of Excel. When I booted Windows again and tried it in that version of Excel, the option to use a formula did appear and your instructions worked perfectly. My experience on this forum has taught me a great deal, including the fact that there is a whole more to Excel than appears on the surface (though admittedly I can barely scratch the surface there), and that a Mac iteration of an app may not be the functional duplicate of its Windows iteration.

I love the Mac's user interface for a lot of reasons, mostly having to do with ergonomics, but I'm wondering if I should switch back to the Windows platform. Not only are Windows computers cheaper but in some cases they seem to have better functionality. Hmmm. Anyway, thanks again for your invaluable help. Even if I were much better acquainted with Excel, I don't think I'd have ever figured this one out on my own.

 

[Joe4]

You are welcome, glad I was able to help.

It does seem that the Windows version of Excel is more robust than the Mac version. Note the quote from this article:

Microsoft Excel on Mac versus Excel on Windows

Explore the differences between using Microsoft Excel on MacOS and Excel on Windows.

www.parallels.com

Heavy Excel users or power users may find that the Windows version has more to offer them — it has more features, better support from Microsoft, and stronger options for VBA coding. However, if you only use the basic capabilities of Excel, the Mac version could be enough.

 

[HowToDoIt]

You are welcome, glad I was able to help.

It does seem that the Windows version of Excel is more robust than the Mac version. Note the quote from this article:

Microsoft Excel on Mac versus Excel on Windows

Explore the differences between using Microsoft Excel on MacOS and Excel on Windows.

www.parallels.com

Then normally the Mac version should work for me. I'll never be a "power user" or get beyond the very basics. Until now I haven't really ever needed to.