Conditional formating formula for duplicates only if in another column

[GDH]

Hi all

I am looking for a way of using conditional formatting across 2 columns of data. I have 2 columns - Debits and Credits.

I would to look in the Debit column and highlight any number in the debit which also appears in the credit column (and then do the same thing in the credit column relative to the debit column). Just using the normal conditional formatting for duplicates results in a number being formatted even if both numbers appear in 1 column - and this is what I am trying to avoid.

At present I am using 2 ranges (each column - Dr's and Cr's) and 2 rules in conditional formatting to achieve this - which is a time consuming process.

At present the formula I have for the different columns are:

=ISNUMBER(MATCH(G6,$F$6:$F$60,0))
&
=ISNUMBER(MATCH(F6,$G$6:$G$60,0))

Is there a way to automate this process with VBA so that I can select the range (both columns) and have this form of formatting applied without me having to apply 2 rules to 2 different ranges.

And if possible - can the blank values be ignored as at present, using my conditional formatting solution above - all blank values are highlighted as duplicates if there is a blank in the other column.

Thanks in advance!
<TABLE style="WIDTH: 132pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=176 border=0><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" span=2 width=88><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 66pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=88 height=20></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 66pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=88> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[Gerald Higgins]

Before you go down the VBA route, have a look at this

=AND(G6<>0,ISNUMBER(MATCH(G6,$F:$F,0)))

This will do two things.
First, it will NOT apply the formating if G6=0.
Second, it refers to the whole of Col F, not just your specified range.
So you don't need to keep updating it.

 

[T. Valko]

Before you go down the VBA route, have a look at this

=AND(G6<>0,ISNUMBER(MATCH(G6,$F:$F,0)))

This will do two things.
First, it will NOT apply the formating if G6=0.
Second, it refers to the whole of Col F, not just your specified range.
So you don't need to keep updating it.

You can reduce that to:

=AND(G6<>0,MATCH(G6,$F:$F,0))

 

[GDH]

Hi Gerald

Code:

=AND(G6<>0,ISNUMBER(MATCH(G6,$F:$F,0)))</PRE>
The <>0 part of the above works great. Unfortunately, this does not let me avoid having to run 2 conditional formatting rules over 2 separate columns.

I wish to the number fomatted in both columns if that number appears in both and therefore I had 2 separate rules each relating to its specific column. For example, column F relates to the Debits, and column G relates to the Credits.

So from what I understand with your formula, I still need to have 2 conidtional formatting rules.

 

[Gerald Higgins]

OK, how about this

=OR(AND(G6<>0,ISNUMBER(MATCH(-G6,$F:$F,0))),AND
(G6<>0,ISNUMBER(MATCH(-G6,$G:$G,0))))

 

[GDH]

Hi Gerald

Even though I applied this formula to both columns for the purpose of conditional formatting - it only highlighted the duplicates in the 1st column and not the second.

 

[Gerald Higgins]

Well when I tried it, it highlighted matched values in both columns.

Like this...

Col F......Col G
-1.........2
-2.........4
-3.........1

The 1s and 2s (both neg and pos) are highlightedm and the -3 and 4 are not highlighted.

Are you sure you have applied the formating correctly ?

Is there something about your setup that you have not told us ?