Matching positive to negative values

[harrison298]

Hi guys!

I was wonding if you could help me? I need a macro in VBA that will go through column Q, and highlight values that are exact opposites

I.e.
1000
123
456
888
-856
564
856
-1000
669
-123
333

Thanks everyone, I really appreciate it!

[NBVC]

You could easily do this with Conditional Formatting instead...

select column Q, go to Home|Conditional Formatting|New Rule

Use a formula to determine which cells to format

enter formula: =COUNTIF(Q:Q,-Q1)

click Format and choose colour from Font tab

[harrison298]

Hi NBVC,

That work perfectly! However it is highlighting the all of the value that match perfectly, what i need is if a value has been matched, it can not link to another value.

For example

1000
3500
6500
1586
1000
-1000
3698
1000
3654
-3654

Here there are 3 positive 1000's and 1 negative 1000, so one +1000 and one -1000 are highlighted and the other two stay the same

Thanks!

[K m]

Assuming your list is in cells A1:A11

Set a CONDITIONAL FORMAT rule with this equation

=IF(ISERROR(IF(A1=VLOOKUP(-1*A1,$A$1:$A$11,1,FALSE),,)),FALSE,TRUE)

Copy this CONDITIONAL FORMAT rule down using COPY and PASTE SPECIAL format

[harrison298]

Hi K m,

I still have the same problem that it is matching all of the exact values, rather than if a value has already been matched, it is not matched again....

Thanks!

[NBVC]

Try:

=AND(COUNTIF(Q$1:Q1,Q1)=1,COUNTIF(Q:Q,-Q1))

[harrison298]

Hi NBVC,

Thats nearly it. It has highlighted most of the values, however it seems to have missed a few....

I have attached the numbers here in a spreadsheet to help. note that these are only the figures, and not the whole speadsheet. in the original spreadsheet, this column is from column Q.

Thanks!

Book2.xlsx

[NBVC]

Can you identify which should/shouln't be hightlighted and why?

[alfie22]

Hi NBVC,

I make it much broad,

let say this is the table: Already match the positive and negative value

ID____Number: Value____Code____Company Name_____Date
1____(-1409810.28)____VJ______Company 1_____Day 2
2____1409810.28 ______VJ______Company 1_____Day 2
3____469747.8 ______RN______Company 1_____Day 2
4____(-469747.8) ______RN______Company 2_____Day 2
5____(-78291.3) ______RN______Company 3_____Day 5
6____78291.3_________RN_______Company 3_____Day 5
7____(-38915.52)______VJ______Company 8_____Day 3
8____38915.52________VJ______Company 8________Day 3
9____(-14102.48)______SY______Company 1______Day 3
10____14102.48_______SY______Company 2_______Day 2

As you can see ID 1 and 2 have the same Number value = to zero. Highlight row to green when Company Name and date is the same "example ID 1 Company is the same to ID 2 company, Also in date
and ID 3 and 4 highlight row into yellow when their company name and date is different to each other.
and as you see in ID 9 and 10 has Code is SY highlight it into blue when it is SY, and the Number value is = to zero

Hope you help us. Many thanks in advance

[FDibbins]

alfie22 welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.