Search code examples
excelexcel-formulaconditional-formatting

Highlight cells based on the value of cells in another column

I have this problem as noted below:

Column A = Part number

Column B = Quantity

Column C = Part number

Column D = Quantity

Using conditional formatting, I would like to highlight if the combination of Part number and Quantity in Column A and B is different to the combination of Part number and Quantity in Column C and D.

Eg:

     Col A   Col B   Col C   Col D  
 1   1111     2      1112     5
 2   1112     3      1111     2
 3   1131     5      1112     5
 4   1122     3      1131     2

To do this, I'd like to set up a couple of 'helper' columns (say E & F) by concatenating Column A & B, C & D.

So essentially, I'd like to take the information from the helper columns E & F, but use conditional formatting to highlight the cell in column B and D.

From the example above, cell B3 and D4 would be highlighted.

Is this possible, and if not, is there are simple alternative? (I don't mind using a macro if need be).

Solution

  • I would use COUNTIFS

    For B1:B4

    =COUNTIFS($C$1:$C$4,A1,$D$1:$D$4,"<>"&B1)

    and for D1:D4

    =COUNTIFS($A$1:$A$4,C1,$B$1:$B$4,"<>"&D1)

    enter image description here