Alternate row color in Excel Solved/Closed

Question

Hello,

How can I get alternating rows in a table to have a different background color (and keep it that way during sorting or filtering) to improve readability?
I had found a procedure but I've lost it.

Thanks

Configuration: Windows XP Internet Explorer 6.0

eriiic · Accepted Answer

Hello,

You can use a conditional format.
Select your area then menu 'format / conditional formatting...'
Select 'The formula is' and enter: =MOD(ROW();2)
(or =NOT(MOD(ROW();2)) if you want the even rows colored
Click on 'format...' to choose your 'pattern' and confirm

eric

Gilles41 · Answer

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?

eriiic · Answer

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

Kloup · Answer

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

eriiic · Answer

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

pépé35530 · Answer

Hello,  Excel 2007 does this very, very well, and with many possible colors.  See you later  pépé

arnaud · Answer

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

Wolverine52 · Answer

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?

rafit jad kuldinger · Answer

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.

Raymond PENTIER · Answer

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... :-) &#9787; Raymond &#9794;

Alternate row color in Excel

10 réponses

Texture issues in enshrouded

Driver his

Peritel to hdmi conversion

How to access the bios on a compaq notebook

Dsi won't turn on anymore

Search for a song with the lyrics

Left mouse click, touchpad click inactive

Codes stronghold crusader

Stuck computer system repair

Disable write protection mp3 player