Capt_Antihero
New Member
- Joined
- Jun 16, 2014
- Messages
- 14
Hello,
I have a problem that I need some help with. I have data in 2 columns and I need to highlight all the amounts that match the list, looping down the list each item at a time, and once the amount is matched (paired) to continue the loop but disregard the paired (matched) items; even if the amount appears twice as it isn't a duplicate but 2 separate entries for the same amount. In the below table I've shown Amount A & Amount B:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Amount A[/TD]
[TD]Amount B[/TD]
[/TR]
[TR]
[TD]$400[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]$600[/TD]
[TD]$135[/TD]
[/TR]
[TR]
[TD]$200[/TD]
[TD]$150[/TD]
[/TR]
[TR]
[TD]$200[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]$135[/TD]
[TD]$400[/TD]
[/TR]
</tbody>[/TABLE]
I would need the macro start in Amount A ("Sheet1" L2) and search through the array of amounts from the start, in this case $400. It would have to find the first $400 listed in Amount B ("Sheet1" O2) Once it finds it's matching pair I would like both cells highlighted in yellow, and to do nothing i.e. leave it the colour it is if it doesn't find a match.
The macro would then proceed to find the next amount being $600 in Amount A and look through Amount B until the pair is found (from top to bottom of the list) Now for the tricky part, I need the macro to continue searching through each array for the amounts listed, and in the example above there is two $200, I need the macro to find the first $200 and / if found, then to still look for the second $200.
Everything I have researched like Instr that only finds the first instance or it would find $200 in $200.20 and call it a match. I've tried using vloopup methods but it only finds 1 match and ignores duplicates, although in my data it wouldn't be a duplicate, it's just two entries of the same amount. I've explained this as best I can I hope it makes sense, please help, I'm not new to VBA but this one has stumped me...
I have a problem that I need some help with. I have data in 2 columns and I need to highlight all the amounts that match the list, looping down the list each item at a time, and once the amount is matched (paired) to continue the loop but disregard the paired (matched) items; even if the amount appears twice as it isn't a duplicate but 2 separate entries for the same amount. In the below table I've shown Amount A & Amount B:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Amount A[/TD]
[TD]Amount B[/TD]
[/TR]
[TR]
[TD]$400[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]$600[/TD]
[TD]$135[/TD]
[/TR]
[TR]
[TD]$200[/TD]
[TD]$150[/TD]
[/TR]
[TR]
[TD]$200[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]$135[/TD]
[TD]$400[/TD]
[/TR]
</tbody>[/TABLE]
I would need the macro start in Amount A ("Sheet1" L2) and search through the array of amounts from the start, in this case $400. It would have to find the first $400 listed in Amount B ("Sheet1" O2) Once it finds it's matching pair I would like both cells highlighted in yellow, and to do nothing i.e. leave it the colour it is if it doesn't find a match.
The macro would then proceed to find the next amount being $600 in Amount A and look through Amount B until the pair is found (from top to bottom of the list) Now for the tricky part, I need the macro to continue searching through each array for the amounts listed, and in the example above there is two $200, I need the macro to find the first $200 and / if found, then to still look for the second $200.
Everything I have researched like Instr that only finds the first instance or it would find $200 in $200.20 and call it a match. I've tried using vloopup methods but it only finds 1 match and ignores duplicates, although in my data it wouldn't be a duplicate, it's just two entries of the same amount. I've explained this as best I can I hope it makes sense, please help, I'm not new to VBA but this one has stumped me...
