CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Paul Cathey
Paul Cathey

Reputation: 13

Excel Conditional Formatting Formula for Comparing Paired Rows?

Goal:

In a single column (EG column C) I have paired rows of data (EG Row 3 and 4 and want to compare C3 and C4 specifically). I am attempting to have a conditional formatting rule highlight those two cells (C3 and C4) when their values do not match.

Issue:

I have worked out a conditional formula (with the help of the AI's) that seems like it should work but is not. It appears when I run it that the formula is only comparing each cell with the cell below it, but not the cell above it as I believe is required to make this work.

Approach:

A conditional formatting formula is executed in each cell to where it is applied. My logic is that we check first if the cell row is even or odd, if it is odd (C3) we want to look at the value below and if that value is not the same as the current cell then it should return TRUE (C3 <> C4) and highlight the current cell. If the cell is even, then conversely we will compare the value with that of the cell above (C4 <> C3) (also I have tried (C4 <> OFFSET(C4,-1,0)). Regardless, when executed the comparison is only ever made from the higher cell down to the lower cell and not the other way...

Here is my current formula. I first tried by splitting the OR into two separate formulas, but that didn't work and I also suspected they might cancel each other out regardless of which was executed first.

=OR(AND(MOD(ROW(), 2) = 1, 
    C3 <> OFFSET(C3, 1, 0)), 
    AND(MOD(ROW(), 2) = 0, 
    C4 <> OFFSET(C4, -1, 0)))

Upvotes: 1

Views: 167

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

I would simplify it to

=ISODD(ROW())*(C3<>C4)+ISEVEN(ROW())*(C3<>C2)

if starting with C3. In C3 just the first half of the formula applies because the row is odd.

Relative addressing applies within conditional formatting, so if you imagine the formula being pulled down into cell C4, it becomes:

=ISODD(ROW())*(C4<>C5)+ISEVEN(ROW())*(C4<>C3)

so the second half of the formula applies because the row is even.

enter image description here

The only caveat would be that the conditional formatting must start on an odd row and finish on an even row, otherwise you would be comparing the first or last cell with a value outside the formatted range e.g.:

enter image description here

By the same logic, your original formula can be made to work like this:

=OR(AND(MOD(ROW(), 2) = 1, C3 <> OFFSET(C3, 1, 0)), AND(MOD(ROW(), 2) = 0, C3 <> OFFSET(C3, -1, 0)))

Upvotes: 1

Brendan Mitchell
Brendan Mitchell

Reputation: 477

Everywhere in your formula where you reference C3 and C4, use C1 (or whatever the topmost cell in your range is) instead.

OR(AND(MOD(ROW(), 2) = 1, C1 <> OFFSET(C1, 1, 0)), AND(MOD(ROW(), 2) = 0, C1 <> OFFSET(C1, -1, 0)))

This question has some good detail on how relative references work in Excel.

Upvotes: 0

Related Questions