How do I do this: If A+B = D+E, make RED.

[daniel981]

I have 5500 or so rows/lines of data with 5 columns. Column A is program1's last names, B is program1's first names, C is just a visual barrier between name sets, D is program2's last names, and finally E is program2's first names.

The first thing I need to do is space out column D and E to correlate with column A and B.

For example:

Please Login or Register  to view this content.

How do I do this? I need to insert a blank spot on line 4D, and 4E so that it will push "Becker" and "Frank" down to line 5D and 5E. But I need to do this for 5500+ people. Is there to automate this process?

THEN ( I know, this is beginning to be too much! sorry! )

I need to find a way to go through each line and see if A+B = D+E, and if it does, make it red.

How, oh how do I do all this?!?

Thank you soooooo much for any help that can be provided. I hope this makes sense...

[Marcelo]

Daniel

Try it,

http://www.j-walk.com/ss/excel/usertips/tip073.htm

Regards from Brazil
Marcelo


"daniel981" escreveu:

>
> I have 5500 or so rows/lines of data with 5 columns. Column A is
> program1's last names, B is program1's first names, C is just a visual
> barrier between name sets, D is program2's last names, and finally E is
> program2's first names.
>
> The first thing I need to do is space out column D and E to correlate
> with column A and B.
>
> For example:
>
> Code:
> --------------------
>
> A B C D E ...
> 1 prog1last prog1first prog2last prog2first
> 2 Adams Bill Adams Bill
> 3 Alberts Mary Alberts Mary
> 4 Bates Joe Becker Frank
> 5 Becker Frank Bodine Howard
> .
> .
> .
>
> --------------------
>
>
> How do I do this? I need to insert a blank spot on line 4D, and 4E so
> that it will push "Becker" and "Frank" down to line 5D and 5E. But I
> need to do this for 5500+ people. Is there to automate this process?
>
> THEN ( I know, this is beginning to be too much! sorry! )
>
> I need to find a way to go through each line and see if A+B = D+E, and
> if it does, make it red.
>
> How, oh how do I do all this?!?
>
> Thank you soooooo much for any help that can be provided. I hope this
> makes sense...
>
>
> --
> daniel981
> ------------------------------------------------------------------------
> daniel981's Profile: http://www.excelforum.com/member.php...o&userid=34718
> View this thread: http://www.excelforum.com/showthread...hreadid=544874
>
>

[Ken Hudson]

Daniel,

You could try this macro solution.

Make a back-up copy of your workbook.
Press Ctrl and F11 to open the Visual Basic Editor.
Select Insert and then Module.
Copy the code below and paste it into the module.
Close the Editor.
Go to Tools > Macro > Macros…
Highlight the macro and click Run.
---------------------------------
Option Explicit
Dim Iloop As Single
Dim RowsA As Single
Dim RowsD As Single
Sub FindMatch()

RowsA = Range("A65536").End(xlUp).Row
RowsD = Range("D65536").End(xlUp).Row
Iloop = 1
Do Until Application.WorksheetFunction.CountA(Range("A" & Iloop & ":E" &
Iloop)) = 0
If Cells(Iloop, "A") & Cells(Iloop, "B") > Cells(Iloop, "D") &
Cells(Iloop, "E") Then
Range("A" & Iloop & ":B" & RowsA).Cut
Range("A" & Iloop + 1).Select
ActiveSheet.Paste
ElseIf Cells(Iloop, "A") & Cells(Iloop, "B") < Cells(Iloop, "D") &
Cells(Iloop, "E") Then
Range("D" & Iloop & ":E" & RowsD).Cut
Range("D" & Iloop + 1).Select
ActiveSheet.Paste
Else
Range("A" & Iloop & ":E" & Iloop).Interior.ColorIndex = 3
End If
RowsA = Range("A65536").End(xlUp).Row
RowsD = Range("d65536").End(xlUp).Row
Iloop = Iloop + 1
Loop
End Sub
--------------------
Please note that the "Do Until" line and the "Elseif" lines have
line-wrappnig. The lines following them need to be added to the ends of those
lines.
--
Ken Hudson


"daniel981" wrote:

>
> I have 5500 or so rows/lines of data with 5 columns. Column A is
> program1's last names, B is program1's first names, C is just a visual
> barrier between name sets, D is program2's last names, and finally E is
> program2's first names.
>
> The first thing I need to do is space out column D and E to correlate
> with column A and B.
>
> For example:
>
> Code:
> --------------------
>
> A B C D E ...
> 1 prog1last prog1first prog2last prog2first
> 2 Adams Bill Adams Bill
> 3 Alberts Mary Alberts Mary
> 4 Bates Joe Becker Frank
> 5 Becker Frank Bodine Howard
> .
> .
> .
>
> --------------------
>
>
> How do I do this? I need to insert a blank spot on line 4D, and 4E so
> that it will push "Becker" and "Frank" down to line 5D and 5E. But I
> need to do this for 5500+ people. Is there to automate this process?
>
> THEN ( I know, this is beginning to be too much! sorry! )
>
> I need to find a way to go through each line and see if A+B = D+E, and
> if it does, make it red.
>
> How, oh how do I do all this?!?
>
> Thank you soooooo much for any help that can be provided. I hope this
> makes sense...
>
>
> --
> daniel981
> ------------------------------------------------------------------------
> daniel981's Profile: http://www.excelforum.com/member.php...o&userid=34718
> View this thread: http://www.excelforum.com/showthread...hreadid=544874
>
>

[daniel981]

Worked perfectly. Thank you very much!

Daniel

Try it,

http://www.j-walk.com/ss/excel/usertips/tip073.htm

Regards from Brazil
Marcelo