Conditional formatting based on multiple cells in a row

abdulhaque

Board Regular
Joined
Dec 2, 2015
Messages
63
Hi all,

Just trying to think ahead of how best to tackle an upcoming task. What can I use to highlight differences in groups of cells in a row? In the example below, you can see two tables, table A = columns A-E and table B = columns G-K. Any row between each table, if found in both tables they are not highlighted. If a row is found in table A but not in table B, the row in table A is highlighted in red. Likewise if a row is found in table B but not in table A, the row in table B is highlighted in blue. The cells can contain anything, including numbers, characters, dates etc. Filtering can be applied by any column.

Thanks

ABCDEFGHIJK
1ab123yyyy6
234htc34htc
3tjd76hhhhh
4oooooab123
5123455jjjp
6yyyy6fghij
7hhhhh4578
8dhd2512345
9fghijey6lo
104578ooooo

<tbody>
</tbody>
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi all,

Just trying to think ahead of how best to tackle an upcoming task. What can I use to highlight differences in groups of cells in a row? In the example below, you can see two tables, table A = columns A-E and table B = columns G-K. Any row between each table, if found in both tables they are not highlighted. If a row is found in table A but not in table B, the row in table A is highlighted in red. Likewise if a row is found in table B but not in table A, the row in table B is highlighted in blue. The cells can contain anything, including numbers, characters, dates etc. Filtering can be applied by any column.

Thanks

Hi!

Try this:

Table A

=ISERROR(MATCH(5,MMULT(--($A1:$E1=$G$1:$K$10),TRANSPOSE(COLUMN(A1:E1)/COLUMN(A1:E1))),0))<strike>
</strike>

Table B

=ISERROR(MATCH(5,MMULT(--($G1:$K1=$A$1:$E$10),TRANSPOSE(COLUMN(G1:K1)/COLUMN(G1:K1))),0))

Markmzz
 
Upvote 0
A small modification.

Table A

=ISERROR(MATCH(COLUMNS($A1:$E1),MMULT(--($A1:$E1=$G$1:$K$10),TRANSPOSE(COLUMN($A1:$E1)/COLUMN($A1:$E1))),0))

Table B

=ISERROR(MATCH(COLUMNS($G1:$K1),MMULT(--($G1:$K1=$A$1:$E$10),TRANSPOSE(COLUMN($G1:$K1)/COLUMN($G1:$K1))),0))

Markmzz
 
Upvote 0
A small modification.

Table A

=ISERROR(MATCH(COLUMNS($A1:$E1),MMULT(--($A1:$E1=$G$1:$K$10),TRANSPOSE(COLUMN($A1:$E1)/COLUMN($A1:$E1))),0))

Table B

=ISERROR(MATCH(COLUMNS($G1:$K1),MMULT(--($G1:$K1=$A$1:$E$10),TRANSPOSE(COLUMN($G1:$K1)/COLUMN($G1:$K1))),0))

Markmzz

Thanks, the blue highlights worked, but the red didn't...

ab123yyyy6
34htc34htc
tjd76hhhhh
oooooab123
123455jjjp
yyyy6fghij
hhhhh4578
dhd2512345
fghijey6lo
4578ooooo

<tbody>
</tbody>
 
Last edited:
Upvote 0
Thanks, the blue highlights worked, but the red didn't...

Hi!

Here all is ok. Look at this:

Array Formula (use Ctrl+Shift+Enter to enter the formula) in F1 and copy down

=--ISERROR(MATCH(COLUMNS($A1:$E1),MMULT(--($A1:$E1=$G$1:$K$10),TRANSPOSE(COLUMN($A1:$E1)/COLUMN($A1:$E1))),0))

Array Formula (use Ctrl+Shift+Enter to enter the formula) in L1 and copy down

=--ISERROR(MATCH(COLUMNS($G1:$K1),MMULT(--($G1:$K1=$A$1:$E$10),TRANSPOSE(COLUMN($G1:$K1)/COLUMN($G1:$K1))),0))


ABCDEFGHIJKL
1ab1230yyyy60
234htc034htc0
3tjd761hhhhh0
4ooooo0ab1230
51234505jjjp1
6yyyy60fghij0
7hhhhh045780
8dhd251123450
9fghij0ey6lo1
1045780ooooo0
11
***************************************************

<tbody>
</tbody>


Ps: delete all CF before to create the CF red and blue.

Markmzz
 
Last edited:
Upvote 0
Hi!

Here all is ok. Look at this:

Array Formula (use Ctrl+Shift+Enter to enter the formula) in F1 and copy down

=--ISERROR(MATCH(COLUMNS($A1:$E1),MMULT(--($A1:$E1=$G$1:$K$10),TRANSPOSE(COLUMN($A1:$E1)/COLUMN($A1:$E1))),0))

Array Formula (use Ctrl+Shift+Enter to enter the formula) in L1 and copy down

=--ISERROR(MATCH(COLUMNS($G1:$K1),MMULT(--($G1:$K1=$A$1:$E$10),TRANSPOSE(COLUMN($G1:$K1)/COLUMN($G1:$K1))),0))


ABCDEFGHIJKL
1ab1230yyyy60
234htc034htc0
3tjd761hhhhh0
4ooooo0ab1230
51234505jjjp1
6yyyy60fghij0
7hhhhh045780
8dhd251123450
9fghij0ey6lo1
1045780ooooo0
11
***************************************************

<tbody>
</tbody>


Ps: delete all CF before to create the CF red and blue.

Markmzz

Thanks a mill! I was applying the formula directly in the CF formula dialog.
 
Upvote 0
Thanks a mill! I was applying the formula directly in the CF formula dialog.

Hi!

It is not necessary to put the formula in the sheet. I put off the conditional formatting too only to demonstrate that it is working.
So, you can put only the formula directly in the CF without any problem.

Markmzz
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top