Forumlas that have shading to alternate every other row which also update when rows are deleted/adde

Jason F

Good afternoon!

I did use a pair of formulas based of digging I did on the forum, both a =ISODD and =MATCH, with an AUTOID column, but I think I'm making it harder.

This sheet is based off a form which will add a row per submission. I'm wanting to alternate the shading every other row via conditional formatting, which it updating when rows are added/deleted.

I had the auto-number column, plus 2 additional columns for the other formulas but I am lost somewhere. Please assist and thank you in advance!!!

Ray Lindstrom

Hi @Jason F,

One of your columns is for checking if the AutoID "is odd". That column should be a checkbox.

Then your conditional formatting would be:

Condition: 'IsOdd Column'

Criteria: 'Is Checked' or 'Is Unchecked' (whichever you prefer)

This format: set your column shading

That should do it. Let me know if this helps, or exactly where you're stuck and I'll try to help you along.

BRgds,

-Ray

Jason F

@Ray Lindstrom , I have to admit that I'm lost.

In order to achieve it every other row, along with it being able to update automatically based on adding or removing rows, I should have an Auto-Number column?

I think my formulas were all wrong as well.

Ray Lindstrom

@Jason F,

No problem. I'll do my best to help you through.

-------------------------

1.) Create your helper columns:

Create an Auto Numbering type column. I created one called "AutoID" and left the settings alone.

Create a "IsOdd" checkbox type column.

On one of your rows, in the "IsOdd" column; insert this formula =ISODD(AutoID@row).

Right click the cell you placed this formula in, and click "Convert to Column Formula".

-------------------------

2.) Set your conditional formatting:

Click to add a new conditional formatting rule.

Click "set condition" and select "IsOdd" Column under "select column for condition"

Click "Checked" under "select criteria"

Click "this format" for your new rule, and select the background shading you would like for your odd rows.

-------------------------

With that you should have alternating gray rows.

Let me know it works out!

BRgds,

-Ray

Jason F

The formula gives me an unparse. And thank you for your help!

Ray Lindstrom

You have to replace the "AutoID" with your own column name.

=ISODD(AutoID@row)

cbredehoeft

@Ray Lindstrom I have used the row ID/check boxes route many times. My question is, how to navigate around the issue of system columns in source sheet when automation is applied. For example, I am moving a row to another sheet that has the same set of columns set up. Is this still possible or do I need to remove the auto row ID from the source sheet?