help on conditional formating please...

[konijay]

Hi,


I have below data.


A B

1 1 1

2 1 2

3 1 1

4 1 3

5 1 1


I need to highlight A2+B2 and A4+B4 in red because they are different(unique).

I have been selecting A1+B1 and doing conditional formating(unique value) and using format painter to copy the format by clicking each line seperately...selecting the whole RANGE(A2:B5) doesn't work...


Is there a easy way to copy the format down?...or using a formula maybe?...


thanks

 

[Luke M]

Select the entire range of info. Then, assuming A2 is the active cell, the conditional format formula is:

=COUNTIF(C:C,C2)=1

 

[konijay]

A B

1 1

1 2 <-highlight these 2 cells

1 1

1 3 <-highlight these 2 cells

1 1


Sorry, maybe i wasn't clear...i want to do above

 

[Luke M]

Ah, thanks for clarifying. CF formula should then be:

=SUMPRODUCT(--($A$2:$A$10&$B$2:$B$10=A2&B2))=1

 

[konijay]

sorry, Luke..it's not working


I am wanting to compare both A column and B column(per row), and if A and B are different, highlight both cells


thanks

 

[Hui]

Konijay

Lukes formula is very close


With A1:B5 selected

Try this instead:

=SUMPRODUCT(--($A$1:$A$5&$B$1:$B$5=$A1&$B1))=1

 

[Luke M]

Wait, just highlight if they are different?

=$A2<>$B2

 

[Hui]

I read it as the combination A2&B2 is unique

 

[sdsurzh]

Hi Konijay,


As per your request the formula suggested by Hui(=SUMPRODUCT(--($A$1:$A$5&$B$1:$B$5=$A1&$B1))=1) is working fine.

Can you please download the below file and check.


http://www.2shared.com/file/lA7-PK7j/Book1.html


Thanks,

Suresh Kumar S

 

[konijay]

Luke,


=$A1<>$B1 worked....


I knew it was something simple...


I wasn't necessarily looking for "1"...just highlight the row with 2 different numbers


thank you

 

[Luke M]

=P


Glad it turned out to be an easy fix!

 

[SirJB7]

Hi, all!

I read different, as if sum of Ax and Bx were not unique.

Regards!