Alternate row color in Excel

You're overcomplicating things with complex formulas for no reason.
I will never understand this expert mentality (which is similar to that of a Linux user) that uses lines of code when the software can do it with just a few clicks.

To shade every other line:
Just select the lines to modify.
Go to Format/AutoFormat.
And choose "List1" in "Table Format."
Finally, click OK.

Wouldn't it have been simpler to include that from the start?

Hello,

Even a year later, your response is interesting.
However, it didn't work during the test.
So I specify that for a conditional formatting starting in cell A1, you need to enter:
=SUBTOTAL(103;$A$1:$A1)=EVEN(SUBTOTAL(103;$A$1:$A1))
as the conditional formatting formula, and then it's perfect.
Thank you, Kloup

Eric

Hello

To color every other row even when filtering, without using macros, you can use SUBTOTAL(103;...).
It counts the non-empty visible cells,
as long as there is a column with data (codes...) on each line,

The function in the conditional formatting is:

=SUBTOTAL(103;$A$1:$A231)=EVEN(SUBTOTAL(103;$A$1:$A231))

"I count the number of non-empty cells in column A, from the first row to the current row.
If it's even, I color it; otherwise, the cell remains white"

See you
Kloup

One can also do Insert/Table: by default, it is created with row alternations. But with Table Tools/Design/Table Style Options, you can alternate the columns...

--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂

Good evening,

uh, I hardly believe it, anyway without using VBA...
But is it worth developing a macro just for that?
eric

Hello,

Excel 2007 does this very, very well, and with many possible colors.

See you later

pépé

Hello,

Thank you Eric for the answer I found on another site as well. But is it possible to keep every other line with a background color after applying a filter?

Hello everyone,

first of all, thank you for all the information that has been shared so far.

I have one last question: how (once the formatting has been applied) can we change the color of colored cells? There might be a need to color some cells afterward, but we can't because the condition has been set.

If anyone has a solution, I'm all ears!

Thank you!

Arnaud

Hello, I want to do the same thing, automatically color every other line when opening a new Excel document.
For version 2011.
Thank you

--
When we open our eyes, we discover the world, but sometimes we have to keep them closed.