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

Herakles

Well-known Member
Joined
Jul 5, 2020
Messages
927
Office Version
  1. 365
Platform
  1. Windows
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.
 
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?
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top