Macro to find Positive Number the same as Negative numbers and highlight them.

[excellearner88]

Hello I am trying to create a macro in which negative numbers which are the same as the positive can be highlighted in a certain column in this case being T. Please advise.

Example

-1
-5
-4
1
2
3
4

In this case 1 and 4 would be highlighted.

 

[jasonb75]

You don't need a macro for that, conditional formatting will suffice.

Use =AND(-MATCH($T1,$T:$T,0),$T1>0) as the condition formula.

 

[excellearner88]

This is also recognizing dups which I do not want to recognize dups

 

[jasonb75]

I'm assuming that duplicates should be recognised if both the positive and equivalent negative value are duplicated.

=AND(-MATCH($T1,$T:$T,0),$T1>0,COUNTIF($T$1:$T1,$T1)<=COUNTIF($T:$T,-$T1))

I think that

=AND($T1>0,COUNTIF($T$1:$T1,$T1)<=COUNTIF($T:$T,-$T1))

Should also work, but I haven't tested either of these in excel.

 

[shg]

What if there were two -4's and one +4 -- would all three be highlighted?

 

[excellearner88]

What if there were two -4's and one +4 -- would all three be highlighted?

No ideally it would net to zero

 

[shg]

See the code and example at HELP me cancel the postive with the same negative values in excel 2010! [SOLVED]

 

[mikerickson]

deleted