Conditional Formatting - Refer to different row depending on whether row number is odd or even.

[Herakles]

I want to be able to refer to a cell in the same row if the formatted cell is in an odd row and to the row above if the formatted cell is in an even row.

How do i do this?

Thanks.

 

[AntMac]

Based on what you have and the confusion of what you're trying to do. Would it help if you had a column just for calculating this? Then maybe you can hide it. But you can have a formula in that column =iseven(row()-1) Then have your formatting be based on if that's one or 0 accordingly?

 

[Joe4]

Where the row is EVEN I need to refer to the row above in the formula and if ODD I need to refer
to the same row. How do I do that?

You need to look at the formula I posted a little closer...

Notice the part of the formula I referenced in red refers to row 3 while the blue part of the formula refers to row 2.

When creating Conditional Formatting formulas, if you select the entire range you want to apply it to first (F3:K12), and then you enter the formula as it applies to the first cell in your selected range (which is F3), Excel is smart enough to automatically adjust it for all the other cells in your selected range.

So, in writing a formula for cell F3, we make a cell reference to row 2 (like M2), Excel knows that you are telling it to always go up exactly one row from whatever cell you are in, for that part of the formula. You don't need to do anything special - Excel looks at the ranges in your formula relative to the cell you are entering them in for.

Try it and see for yourself!