How to use VBA or Macro, to Find and just Higlight many offsetting values - +200 and -200

[Ram_G]

Hi Excel Experts,

Can any one, please help me with this..

I am having a excel spreadsheet, which contains may offsetting values; +150 -150, and +1998 -1998, in a column, which I need to find and highlight and mail accordingly.

Here, description-which is POLICY NO. is of no priority. Because, by observation, two offsetting values, comes in more or less same policy no.

Kindy assist me on this, as most VBA code I tried, showed debug error. Please help me with a VBA for the simple example provided in attached file.

In actual work, there are many rows and different currencies, where I can use filter. But, to find offsetting values in about 500 rows takes time, and chances of error is more.

Appreciate your reply.

Thanks & Regards
Ram

[Olly]

Okay - two problems with your attachment:

1) Syntax error on line 7. Insert a space into AndAbs

2) I presume you are selecting cell B2 then running the macro? This will fail, as it tries to calculate the ABS value of cell B1 - which is text. Simplest solution is to delete the text from cell B1.

[excelliot]

HI

Check this.. You can highlight only duplicated item and also both duplicated items.

Cheers!!

[excelliot]

Hope this worked for you??

[Ram_G]

Hope this worked for you??

Hi Excelliot, Sorry for late reply.. Many thanks for quick support.. Learnt a lot from you, regarding VBA Codes.

But, need one more help..


Your supporting excel file was brilliant, and helpful. Thanks..

The way + and - same value gets highlighted is excellent.


HELP NEEDED:

Can you please, help me in getting only one + value and same - value (Only One) to get highlighted.

No Duplicates, No Multiple same - values. Just one + and -, like -1000 and +1000. Where as rest 5 1000's should NOT BE INCLUDED , as there are no - value of similar amount.

Attached the Re-worked file;L3 - L34 PERFECT, but, where as you can see, L51 & L52 and Also Cell L64 - L68, AND L70, whose net is not zeros, as they are duplicates. (Font Color as Red)

Tried to get it work, lack technical knowledge. If any equation is incorrect. Do advise.

Appreciate your reply.

Regards
Ram

[excelliot]

Pl check.

Cheers!!

[Ram_G]

Pl check.

Cheers!!

Hi Excelliot,

Wow; Just Impressive, to see how u pulled it out.. It works excellent.. During my effort to get answers; found same cases which were unsolved from some few 2002 post, but u resolved it.

You are really unique and Genius..


I Believe, this would be the last question on this thread. Spent time, can only do manually. For you it would be simple.

Can u suggest, if any way; to copy color formatting automatically; that is; to highlight L Column - Amounts; the same way, keeping reference as E Column.

At times have 500 - 700 rows, so highlight on amounts is preferred at that time, as further processing is needed.

So, some few line Macro codes, which copies E column; color formatting at end and applies similarly to L column. Helps

Your Fan
Regards
Ram

[excelliot]

Great!!

Replace code in module to below one:

Please Login or Register  to view this content.

Cheers!!

[Ram_G]

Hi Excelliot,

Thanks a lot.

It works perfectly. Task of an hour can be completed, in just 9 - 15 mins, with no chance of human error.

Appreciate the help & support you have given me.

Excelforum, rocks because of people like you.

Regards
Ram

[excelliot]

Thanks Buddy..

I am just a drop in an ocean..

Cheers!!

[Ram_G]

Thanks Buddy..

I am just a drop in an ocean..

Cheers!!

Hi Excelliot,

Thanks for assistance.

Stuck again, in similar tricky situation. . Was trying to create a standard blank file, with Pre-saved macro.

So, that everyday; whenever we pull the data from database, can directly paste and work on copied file of Standard file

Getting the Run - Time error 457. in .Add k, e address.


Kindly assist. Attached are the two files, for reference.


Regards
Ram.

[excelliot]

What are input column? Earlier it was E, is it changed now??

[excelliot]

Check this..It was success on sheet Only two offset Value..

Please Login or Register  to view this content.

Cheers!!

[Ram_G]

ITS BEEN TWO MONTHS.. my personal PC display went off.. Fixed recently.. Thought of thanking once again for earlier post..

Use it daily.. Macro working excellent.. Excelliot.

When any real progress is made, we unlearn and learn anew what we thought we knew before.

Cheers..

Attached the final product [Blank]..

[excelliot]

Thanks buddy for acknowledgement.