We help IT Professionals succeed at work.

Using conditional formatting

zhshqzyc
zhshqzyc asked
on
I used it but forget it.
I want to compare the two cloumns such "B" and "C", "D" and "E" etc.
Need steps. PC1-FinalReport.xls
Comment
Watch Question

Most Valuable Expert 2013

Commented:
Do you want to see if they are the same or not the same? For the first try this:

Select column B and use Conditional Formatting > Manage Rules > New Rule > Use a formula to determine which cells to format > =B1=C1

Choose format > OK

For highlighting differences change to =B1<>C1

You can then select columns B and C and use format painter (little brush button) to copy format to D/E, F/G etc.

regards, barry

Author

Commented:
I want to highlight differences, but copy one by one is too complicated. Can I select the entire region?
Most Valuable Expert 2013

Commented:
OK, to do that with one formula you can select the whole range and use

=B1<>C1

......but then that will also compare C to D and E to F etc. so if you only want B to C, D to E etc then select whole range B to CS use this formula

=AND(B1<>C1,MOD(COLUMN($B1)-COLUMN(B1),2)=0)

See attached - you can see the effect in headers (you can remove that if you want) and some other cells

regards, barry
PC1-FinalReport-barry.xls

Author

Commented:
I want to color both cells rather than just one. Can you modify the formula?

Author

Commented:
And if one is empty, don't compare them.
Most Valuable Expert 2013
Commented:
OK, I used 2 conditional formats so that one works on alternate columns and the other formula on the others......this one

=AND(C2<>"",B2<>"",B2<>C2,MOD(COLUMN($C2)-COLUMN(C2),2)=0)

and then very similar....

=AND(B2<>"",C2<>"",B2<>C2,MOD(COLUMN($B2)-COLUMN(B2),2)=0)

applied to slightly different ranges. I applied up to row 1000, for more or less rows change the "Applies to" range, see revised attachment

regards, barry
PC1-FinalReport-barryv2.xls