CODEWITHSUNDEEP
excel
Clarinetist
Clarinetist

Reputation: 1187

Conditional formatting of two rows in Excel through row comparison

Consider the following data:

enter image description here

I would like to compare each cell in row 1 to the adjacent cell below in row 2, using red for inequality and green for equality. A similar principle applies for rows 3 and 4. I.e., the output should be

enter image description here

The data I have in reality have more rows and columns than in this toy example, so the less manual work that has to be done, the better. The data I have are small enough that I would be fine pointing and clicking for results. I've tried using Conditional Formatting for this, but haven't been able to figure out its formulas.

I would prefer a non-VBA solution if possible.

I don't necessarily need the provided solution to be general, as long as it's clear to me how it can be generalized. I.e., it needs to be obvious how to adjust the solution when the data start at a different row or column, other than cell A1.

Upvotes: 0

Views: 429

Answers (2)

jsheeran
jsheeran

Reputation: 3037

The following formula will format all pairs of nonequal cells:

=OFFSET(A1,1-2*MOD(ROW()+1,2),0)<>A1

Create a second rule with <> replaced with = to format the equal cells.

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152505

Fill all the cells red then use this rule to turn them Green

=AND(A1<>"",OR(AND(MOD(ROW(A1)-1,2)=0,A1=A2),AND(MOD(ROW(A1)-1,2)=1,A1=INDEX(A:A,ROW(A1)-1))))

enter image description here

Upvotes: 2

Related Questions