I'm trying to compare and highlight all the rows that have the same value (Column G) after each other. My current formula is =$G2=$G3 for the conditional formatting, see green color.

This is my result:

My issue:

I would like to offset the highlight result, so that always the "second result" is marked. So if G4=G5, then I want G5 to be highlighted. Now I only get G4 is marked.

Is it possible to offset the result by one row or some other suggestion? I've tried to use =IF($G2=$G3;OFFSET(G2;1;0)). I also used a dummy column (Column I) to offset the result and base the conditional formatting upon, but would be neat to have it in the formula.

Table

| Index | Account     | Date       | TransType | SymbolName | Qty | Price | Fee   |       |
|-------|-------------|------------|-----------|------------|-----|-------|-------|-------|
| 119   | Portfolio A | 2015-03-30 | DIV       | AA         | 66  | 0,05  | 0,99  | FALSE |
| 120   | Portfolio A | 2015-04-28 | DIV       | BAC        | 42  | 0,23  | 2,898 | FALSE |
| 121   | Portfolio A | 2015-05-15 | Buy       | MSFT       | 42  | 16,58 | 12,95 | TRUE  |
| 123   | Portfolio B | 2015-05-15 | Buy       | MSFT       | 42  | 16,58 | 12,95 | FALSE |
| 122   | Portfolio B | 2015-05-15 | Deposit   | * Cash     | 1   | 732   |       | FALSE |
| 131   | Portfolio A | 2015-08-03 | DIV       | APPL       | 42  | 0,18  | 2,26  | TRUE  |
| 132   | Portfolio B | 2015-08-03 | DIV       | APPL       | 42  | 0,18  | 2,26  | FALSE |

CodePudding user response：

Well, you may try in this was as shown in the image below,

• Select the whole range, i.e from A2:H8 as shown in the above post

• From Home Tab --> Click Conditional Formatting Under Styles Group

• Click New,

• New Formatting Rule Dialog Box Opens,

• Select the Rule --> Use A Formula To Determine Which Cells To Format

• Place the below formula, in the edit rule description

=($D2=$D1)*($G2=$G1)=1

• Click Format --> Choose preferred Font or Fill Color From Font or Fill Tab

• Press OK --> OK