Rafi
Rafi

Reputation: 49

Conditional Formatting Based on Prior Columns Data

I read another post (Conditional Formatting based on relative column) but the answer did not work for me and I am unable to comment due to low reputation.

I have a reporting spreadsheet where column J represents data as of last update and column K represents data from current update. Spreadsheet Example and I am trying to see the changes in any data points using conditional formatting.

The problem arises when Excel starts to compare a date to a number (as seen in column L compared to column K). I want to be able to dictate to Excel that it should not evaluate one after the other (K-J;L-K) but that it should move in groupings (K-J;M-L;O-N) essentially skipping over the prior used column in the evaluation.

Any and all ideas appreciated

Upvotes: 2

Views: 63

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34245

Try

=OR(AND(J2<>K2,ISEVEN(COLUMN(J2))),AND(I2<>J2,ISODD(COLUMN(J2))))

or

=OR(AND(J2<>K2,ISODD(COLUMNS($J:J))),AND(I2<>J2,ISEVEN(COLUMNS($J:J))))

is better because not affected by columns being inserted/deleted before column J.

enter image description here

Upvotes: 1

Related Questions