Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I do this: If A+B = D+E, make RED.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I do this: If A+B = D+E, make RED.
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I do this: If A+B = D+E, make RED.
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I do this: If A+B = D+E, make RED.
Worked perfectly. Thank you very much! Marcelo Wrote: Daniel Try it, http://www.j-walk.com/ss/excel/usertips/tip073.htm Regards from Brazil Marcelo -- daniel981 ------------------------------------------------------------------------ daniel981's Profile: http://www.excelforum.com/member.php...o&userid=34718 View this thread: http://www.excelforum.com/showthread...hreadid=544874 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make rulers available in excel! | Excel Discussion (Misc queries) | |||
Can I make formulas more flexible? | Excel Discussion (Misc queries) | |||
Shuffling Data To Make Lists Match | Excel Worksheet Functions | |||
make hidden window or workbook visible without specify the name | Excel Worksheet Functions | |||
make cell contents equal to null value - not blank, but empty | Excel Worksheet Functions |